Re: Load Data to Oracle from SAS?

oliver <oliver_at_agyinc.com> wrote in message news:37F24EB5.AFA92229_at_agyinc.com...
> Hi,
>
> Does any one have the experience to load data to Oracle database
> directly from SAS?
>
> I have to do a lot of thing using SAS before I load the processed data
> to the database. I want to know that is there any simply way that I can
> stay in the SAS to do all the data processing and analysis and load to
> data to the Oracle with out leave SAS?
> I do have the SAS/Access for Oracle, but it is totally new thing for me,
> I have not be able to find my way yet.
>
> Any suggestion will be appreciated!
>
> --
> Oliver
>

If SAS/Access to Oracle is installed properly it is a very simple matter: All you need is:
- a user name and password; and
- name of Oracle database instance

proc sql;
  connect to Oracle (user=<oracle user name> orapw=<oracle user password> path="@<oracle database instance name>");

  create view <sas name> as
  select *
  from connection to Oracle
  (select * from <oracle table>)
  ;

  disconnect from Oracle;
quit;

Use <sas name> in your SAS procs. As far as SAS procedures are concerned <sas name> is just another data set. The SQL view takes care of everything (connection-wise) for you.

i.e.
proc print data=<sas name>;
proc fsview data=<sas name>;

To send data to Oracle you can use PROC DBLOAD (fastest if many results) or you can EXECUTE (insert ...) statements. Some SAS Views (and Access descriptors) allow edits to go back to the Oracle table, not sure of the specifics.

caveats and hints (6.12):
Oracle column names are mapped to similar SAS eight character variable names Oracle column names become the SAS variable label If you are planning on reusing the data over and over in SAS, consider creating a table from the Oracle data instead of creating a view. It the Oracle table has many columns, select only those of interest. Oracle version 7 does not have a SQL case statement (although Oracle decode function can work in a pinch)
Use the EXECUTE () statement in SAS proc SQL to issue Oracle specific commands to the Oracle database (i.e. as if you were in an Oracle Sql/plus session)
Oracle number typed columns become SAS numeric variables formatted best22. Oracle character typed columns become SAS character variables of same length (<=200 char)
Oracle date typed columns become SAS numeric variables formatted datetime18., use SAS datepart() function to pull of the date-only [be sure to format the result as DATE7. or MMDDYY10.] Oracle rowid types columns cannot be sent to SAS (this is only a problem if you select * on Oracle tables with a rowid typed column) Oracle long typed columns contain upto 2GB of arbitrary byte data, not conducive to SAS variables. Some information stored in longs is useful and can be transferred into SAS using an Oracle procedure to create an Oracle table that amenable to SAS.

Visit www.orafaq.org for helpful information about Oracle.

Visit http://www.bf.rmit.edu.au/~orafaq/dict.html for a list of Oracle system tables that can tell you about almost anything in Oracle. (i.e. similar to SASHELP dictionary views (VTABLE, VCOLUMN, etc..)). e.g., you don't know the Oracle table name exactly, find it in SAS using proc sql;
connect ...;
create view oratabls as select * from connection to Oracle (select * from ALL_TABLES);
disconnect ...;
proc fsview data=oratabls;
run;

Richard Received on Thu Sep 30 1999 - 10:19:14 CDT