> 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!
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
connect to Oracle (user=<oracle user name> orapw=<oracle user password>
create view <sas name> as
from connection to Oracle
(select * from <oracle table>)
disconnect from Oracle;
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.
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
Oracle number typed columns become SAS numeric variables formatted best22.
Oracle character typed columns become SAS character variables of same length
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
create view oratabls as select * from connection to Oracle (select * from
proc fsview data=oratabls;