Sas proc sql connect to oracle schema

I am writing a SAS program and using proc sql to select the data from an Oracle table. I have a 'connect' statement defined with the userid & password assigned to the the Oracle schema and the path the tables reside in for SAS.

connect to oracle(USER=U100_DEVT

I have 2 issues:

1) I need to read in data from 2 different Oracle schemas each having their own userid/passwords. Whenever I added a second connect statement to my code, I got an error saying that the Oracle connection had already been made. How can I access the Oracle data from 2 different schemas?

2) I need to read data in an Oracle schema AND a SAS dataset. I put in the 'connect' for the Oracle schema(tables) and then used the LIBNAME statement for my SAS dataset. I get an error saying my SAS dataset can not be found. It seems to think the SAS dataset is an Oracle table because of the connect statement.

This is the first program I have written in SAS and I am struggling with how to make these connections! Any suggestions would be appreciated!

7 REPLIES 7 Onyx | Level 15

Mark as New
Bookmark
Subscribe
Mute
RSS Feed
Permalink
Print
Report Inappropriate Content

Re: How to access Oracle data residing in separate schemas in SAS? Posted 12-17-2012 01:58 PM (7587 views) | In reply to ncsthbell

I would like to know how this can be achieved in "pass-thru" as well. For what I know, both of your questions can be addressed easily using libname engine:

Run following statement twice, with different libref, user, password etc, then you will have two library, and you can treat them just like SAS libraries.

libname ora1 oracle user=xxx password=xxxxx

Rhodochrosite | Level 12

Mark as New
Bookmark
Subscribe
Mute
RSS Feed
Permalink
Print
Report Inappropriate Content

Re: How to access Oracle data residing in separate schemas in SAS? Posted 12-18-2012 01:47 PM (7587 views) | In reply to Haikuo

For 1, Haikuo's approach is likely the only one that will work due to the two ID's. The downside is that the data are pulled to the SAS server to join with the two schemas.

SAS might be smarter than I am giving it credit for. Use SASTRACE to see the code that it actually sends to Oracle.

Quartz | Level 8

Mark as New
Bookmark
Subscribe
Mute
RSS Feed
Permalink
Print
Report Inappropriate Content

Re: How to access Oracle data residing in separate schemas in SAS? Posted 12-19-2012 07:42 AM (7587 views) | In reply to Doc_Duke

Doc, thanks for your reply. I noticed that when I coded in the second schema that the performance slowed down tremendously so what you stated makes sense. SAS must have pulled in the data to do the joins of the 2 schemas. I have asked our dba's to add the 1 table I need from the one schema into the other schema and they will not do it so I am stuck on trying to figure out how to handle this in the best way without performance being affected. I am dealing with very large data.

I tried using a 'connect' to the first Oracle schema and it was quick. Then I created a SAS dataset for the other oracle table in the other schema. I had a hard time getting it to work as I am new to this and do not really understand how the connects & libname things work together. I wound up removing the 'connect' to oracle and I just put a libname statement in for the SAS dataset and it work, but again, it was slow.

Any recommendations on how to accomplish this in the most efficiend method?