Date:         Mon, 21 Jul 2008 03:03:17 -0700
Reply-To:     Joep Steeman <jsteeman@BUSINESSDECISION.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Joep Steeman <jsteeman@BUSINESSDECISION.COM>
Organization: http://groups.google.com
Subject:      Re: Change/update/modify existing Oracle tables with the SAS data
              step
Comments: To: sas-l@uga.edu
Content-Type: text/plain; charset=ISO-8859-1

On 21 jul, 10:40, a...@uni-greifswald.de (Dietrich Alte) wrote:
> Dear SAS-lers,
>
> BACKGROUND: We are using oracle (with a web-frontend) as backend for
> data-entry in a large epidemiological study. SAS used to be our fine
> tool for data cleaning and finishing for scientic use of the data in the
> past. We have several personnell, who ara good in SAS, but don't know
> any "oracle". So now we are discussing with the oracle heads, whether
> and how we can change/update/modify existing oracle tables from SAS
> using the data step.
>
> AIM: We want to use SAS and the data step for all the nice features, but
> work on oracle tables as if working with SAS-tables.
> How do people "normally" change existing oracle tables from SAS?
>
> SYSTEM: It is SAS 9.1 on WIN-XP. We have licensed access to oracle,
> access to odbc etc.
>
> Any hints welcome!
>
> Regards
>
> Dietrich
>
> --
>  DIETRICH ALTE, Dipl.-Statistiker, Dr. rer. med.
>  Wiss. Projektmanager "Study of Health in Pomerania (SHIP)"
>  Institut für Community Medicine - SHIP/KEF
>  EMA-Universität Greifswald - Medizinische Fakultät
>  Walther-Rathenau-Str. 48, D-17475 Greifswald, Germany
>  URL    ship.community-medicine.de
>  Phone  ++49(0)3834-867713, Fax ++49(0)3834-866684

Dietrich,

If you also have access to Oracle you can make a libref to your Oracle
database as in:

libname oradb oracle 'your_oracle_location' schema='schema'
userid='userId' password='passwd';

You can then use your Oracle tables as SAS datasets as input datasets
like:

data oracle_table;
  set oradb.oracle_table;
run;

In order to modify: use proc sql as in:

proc sql;
  update table oradb.oracle_table;
  set var = value
  where condition
  ;
quit;

Or you can use SQL pass through.

You may want to study the acess libname statement.

I hope this is helpful.

Regards, Joep