Monthly Archives: February 2016

Connect SAS to Oracle

I now work in an organisation where they often use SAS as a means to maintain Oracle tables. For some reason, people approach me to create such connection. Dunno why but they seem to think that I can do this. I developped a method how to set this up.

Generally speaking, SAS offers three ways to connect to an Oracle database.
The first one is to use the entry from TNS Names. Let us assume that OBIEE is a TNS entry. One may verify whether this is true by invoking the command from the command line: tnsping OBIEE. If one sees a server and portnumber being returned, OBIEE is a tns names entry. The SAS command to connect an Oracle scheme is: libname OBIEE odbc user=prodney password=Admin123 datasrc=OBIEE;.

The second option is to use an ODBC connection. Let us assume that OBIEE is an ODBC data source. One may verify this from any ODBC client, say Excel. If one is able to connect Excel to Oracle via the ODBC connection OBIEE, one knows that OBIEE is a valid ODBC connection. The SAS statement is: libname OBIEE odbc user=prodney password=Admin123 datasrc=OBIEE;.

A third option is to use a connection clause in the proc sql. As an example, one may think of:

proc sql ;
connect to odbc(required='dsn=OBIEE;uid=prodney;pwd=Admin123');
create table work.deel1 as select * from connection to odbc(select * from Feit);