CODEWITHSUNDEEP
sas
Tpk43
Tpk43

Reputation: 331

Connecting to Oracle from SAS

This how I configured my LibName 

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)))'

And to fetch data below is the code I am using 

PROC SQL;
    connect using OracleSAS AS OracDB;
select * from connection to oracle
(select * from ENTITY_DATES_WK13);
disconnect from OracDB;
quit;

I am getting the error OracleSAS is not a SAS name & Error in the LIBNAME statement , I am fairly new to SAS..!!

Upvotes: 1

Views: 20269

Answers (4)

Tpk43
Tpk43

Reputation: 331

At last I ended doing something like this..,

%let usr ="pradeep"
%let pwd ="******"
%let pth = "ORACLEPATH"

%put path: &path;

proc sql;
connect to oracle(user = &usr
                  password =&pwd
                  path =&pth buffsize=5000);

/* my code */

Upvotes: 0

Tom
Tom

Reputation: 51611

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

OracleSAS is 9 characters.

Use something shorter.

Upvotes: 1

Kiran
Kiran

Reputation: 3315

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;

Upvotes: 4

Reeza
Reeza

Reputation: 21274

When you set up a libname you don't need the Connect portion of the PROC SQL. You're 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 can't use SAS functions or data.

Upvotes: 1

Related Questions