SAS Connectivity to Oracle

In order to process data from Oracle tables, it is necessary to install the Oracle Instant Client on your local machine where SAS is installed. Eithout the Instant Client you will not be able to access your Oracle data.

The ITS Database Administrators have set up a page to assist in setting up Instant Client. Please review the information at the following link:

http://nau.edu/its/services/database/clientinstall/oraclient1132bit_win.aspx

Once Instant Client is installed you may then use the ACCESS facility for Oracle. You should assure that ACCESS for Oracle is installed by checking your setup with the following SAS code:

proc setinit noalias; 
run;

If the ACCESS module for Oracle is not installed you will need to install this module.

To access your Oracle data you may use the following example code. First, the LIBNAME statement may be used to check the functionality and access to your database.

libname oralib oracle user='myusername' password='mypassword' path='ITSPRD';

where myusername is the username supplied by ITS with the password mypassword.

Once you are able to successfully connect to the database, you are then readyb to start accessing the data.

To further test you may use the following:

proc sql;
connect to oracle (user='myusername' password='mypassword' path='ITSPRD');
create table test1 as select * from connection to oracle;
(select * from mytablename);

The statement (select * from mytablename) may be any sql statement to select the data you require from a single table or a set of joined tables.