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
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
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.