Proc Sql pass through query of multiple tables on multiple ORACLE servers

[jsaspelin]

I have want to JOIN two (2) "ORACLE" tables from two (2) different ORACLE databases using a SAS PROC SQL pass-through query.  We are not allowed to use an ODBC connection.  Is this possible?  If so, does anyone have piece of PROC SQL code that I can refer to retrofit my query?

I am running the following SAS Studio Version.

SAS Studio 3.5 - Enterprise Edition

SAS release: 9.04.01M3P06242015

Thank you.

John Aspelin

[SuryaKiran]

Hello,

Welcome to SAS Community!

First: What connection options do you have?

 You mentioned, ODBC not allowed, then check other available options. Do you have SAS/Access interface to Oracle licences. 

This code will tell you that. 

proc setinit;run;

Second, deciding whether to use LIBNAME or Pass-Through depends on your queries. Mostly LIBNAME is preferred, unless you have some performance issues. I personally use pass-through for processing the queries in-database (temp tables in database and final output to SAS).

Please refer to this document for LIBNAME & Pass-Through : http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001386257.htm

[jsaspelin]

SuryaKiran,

I ran the proc setinit;run; and it came back that YES it shows that I do have SAS/Access Interface to Oracle.

As for running queries...we are being asked to run SAS PROC SQL queries against a "clone" of the original databases.  Some of the db tables have 30-100+ million rows in them and standard PROC SQL queries, even for very small data sets (35 rows) may take 45 minutes to generate, if using a LEFT JOIN.  I have been using DATA steps and other techniques with some success.  I have started using PASS THROUGH queries because all the heavy processing is going to be done on the server side and not through SAS.  It has made a world of difference.  However, I have tables that reside in multiple database that I need to JOIN together.  I have been using LIBNAME to reference schemas within multiple db's (not using pass-through queries) but they are PAINFUL to wait for. 

I will look at the link you provided to see if that sheds anymore light on my issue.

Thank you very much for your reply.

John

[Reeza]

When you use PASS THROUGH you're using Oracle code and literally passing it to the Oracle server. So, this becomes an Oracle isssue, not a SAS issue. Does Oracle have a way to connect the two data bases?

Otherwise, you will need to set up an ODBC connection to each and then do the query, BUT across tables means that SAS will be downloading the full files and then doing the analysis which is quite inefficient. Depending on the queries there may be ways to chain them together using macros or macro variables. You would need to explain your actual query for this option and it also assumes that you can download information from the servers, aggregate or otherwise.

[LinusH]

One quite recent use case (11g) it was clear that intra-Oracle-DB-connections were really slow, so it was faster do join the tables outside Oracle!

But you might want to involve your DBA, and do some test executions.