How to access Oracle data residing in separate schemas in SAS?

[ncsthbell]

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

PASSWORD="{SAS002}7A179F4D2CBCC75407LIS87023333D8144387AEC4A20A7DC4DEA5C6152"

path=devt_tbls);

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!

Thanks

[Haikuo]

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

path=xxx schema=xxx;

Haikuo

[Doc_Duke]

ncsthbell,

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.

Doc Muhlbaier

Duke

[ncsthbell]

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?

Thanks

[SASKiwi]

1) One way to read from different schemas is to connect to Oracle with a LIBNAME statement and use a SAS DATA step. This also works for SAS SQL:

LIBNAME devt Oracle path = 'devt_tbls' user = xxx password = yyyy;

data new;

  set devt.table1 (schema = schema1)

       devt.table2 (schema = schema2)

      ;

run;

2) How big is your SAS dataset? The most efficient way to join a SAS dataset to an Oracle table is to load the dataset into Oracle as a temporary table and join inside Oracle. If the table is small and you are only using it to select rows you can build an SQL IN list (where column in ('a','b','c'......) from the SAS table into a macro variable and then use this in your Oracle query.

[SandorSzalma]

If you have all data in one database,  your Oracle user can get select grants to the objects in the two other schemas.

(--select 'GRANT SELECT ON SCHEMA_1.' || table_name || ' TO YOUR_ID;' from all_tables where owner='SCHEMA_1'; )

you can use your user in the libname statements: (same like from Hai.Kuo)

libname schema_my oracle path=db schema=my authdomain=authmy;     

libname schema_1 oracle path=db schema=one authdomain=authmy;     

libname schema_2 oracle path=db schema=two authdomain=authmy;

proc sql;                                                                                          

  connect to oracle (                                                                              

  authdomain=authmy path=db" 

  );                                                                                               

create table my.myTable as                                                                    

  select                                                                                           

    *                                                                                              

  from connection to oracle(                                                                       

    select * from one.TableName                                                             

  );                                                                                               

  disconnect from oracle;                                                                          

quit;                                                                                              

[ncsthbell]

SandorSzalma,

I am not clear on this:

If you have all data in one database,  your Oracle user can get select grants to the objects in the two other schemas.

(--select 'GRANT SELECT ON SCHEMA_1.' || table_name || ' TO YOUR_ID;' from all_tables where owner='SCHEMA_1'; )

you can use your user in the libname statements: (same like from Hai.Kuo)

libname schema_my oracle path=db schema=my authdomain=authmy;     

libname schema_1 oracle path=db schema=one authdomain=authmy;     

libname schema_2 oracle path=db schema=two authdomain=authmy;

Are you saying that I need to put the 'select grant' statement in my code?  What I am writing will be going into our production environment and they will not permit my userID to be in the code. 

Also, as Doc's reply stated above, SAS will pull in both schemas to do the joins. This has affected the performance so this may not be the optimal method for me to use.

[SandorSzalma]

ncsthbell,

I say only that you do not need 3 database connections (usernames/passwords) if you have everything  in one database.

After the execution of this SQL-script, you can manage in your SAS-program the Oracle connections (only) with your access.

Regards,

Sándor