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 parameters from the Oracle database. Let us assume that OBIEE is a SID entry. One may verify whether this is true by invoking the command from the command line: tnsping entry_from_tnsnames.ora. If one sees a server and portnumber being returned, along with a SID entry, one may use such parameters in the SAS libname definition. The SAS command to connect an Oracle scheme is: libname mydblib oracle user=”C##HR” password=ine12345 path=”HPDoos:1521/OBIEE”;. Herein, HPDOOS is the server being used, 1521 the port and OBIEE the SID entry.
One may use such data to copy the content of an Oracle table into a SAS daset, such as:
libname mydblib oracle user="C##HR" password=ine12345 path="HPDoos:1521/orcl"; data sasuser.etos; set mydblib.etos; run
Herein, the content of an Oracle table is copied into a SAS dataset (sasuser.etos).
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);