Connecting to Oracle from SAS

Connecting to Oracle from SAS

connecting to oracle or any dbms can be done libname or by explicit pass through. Libname method is used to access oracle table(or any dbms) in SAS(tables are usually moved to SAS). Explicit method ( using connect statement) where in query is directly sent to Oracle(or dbms mentioned). This methods are not interchangeable for oracle(or anydbms) table and hence you got error.

below is libname method

 LIBNAME OrcaleSAS ORACLE USER=UserName PASSWORD=pwd*** PATH = 
   (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host.unix.####.com)(PORT=1521))
    (CONNECT_DATA=(SERVICE_NAME=prod.tk.com)))   
 libname sastab /whatever path/;


 proc sql;
create table sastab.tablename as
select *
 oratable.tablename
quit;

below is explicit pass through method

 proc sql;    
  connect to oracle as myconn (user=smith password=secret 
   path=myoracleserver); 

   create table sastab.newtable as
  select * 
     from connection to myconn       
        (select *          
           from oracleschematable);  

      disconnect from myconn; 
    quit; 

The name you use for your library, called the libref, can only by 8 characters long.

OracleSAS is 9 characters.

Use something shorter.

Connecting to Oracle from SAS

When you set up a libname you dont need the Connect portion of the PROC SQL. Youre mixing two methods of connecting to a SQL database into one.

Assuming your libname worked correctly you can query it the same as you would any other SAS table at this point:

PROC SQL;
  create table want as
  select * from ENTITY_DATES_WK13;
quit;

proc print data=want(obs=5);
run;

The other method is pass through which means the query is passed fully to the server to run on that side. This means the inside query needs to be Oracle compliant and you cant use SAS functions or data.

Leave a Reply

Your email address will not be published.