topic using PROC sql with 2 tables from two different sources in SAS Procedures

using PROC sql with 2 tables from two different sources

teg_76 — Wed, 15 Feb 2012 17:40:17 GMT

Hi Everyone,

How do you work with two tables from two different sources within the same PROC SQL statement? In the example below, the DNT.Patient table is from an oracle database, while the test table is a temporary dataset in the work folder. It doesn’t see the test table when I run the code.

Thanks!

PROC SQL;

CONNECT TO ORACLE (PATH = "xxxxx" USER = xxxxx PASSWORD = xxxxxx);

CREATE TABLE COMBINED AS

SELECT * FROM CONNECTION TO ORACLE

(SELECT a.PATIENT, b.CHART

FROM DNT.PATIENT a

Left outer join

Work.test b);

RUN;

using PROC sql with 2 tables from two different sources

SASKiwi — Wed, 15 Feb 2012 19:18:13 GMT

You are using passthru SQL which means the query is running on the Oracle database where the table TEST will not exist. The solution is to read the PATIENT table into SAS first, then join. This can be done implicitly by changing your query to SAS SQL and connecting to Oracle using a LIBNAME statement:

libname dnt oracle path= "xxxxx" user = 'xxxx' password = 'xxxx';

PROC SQL;

CREATE TABLE COMBINED AS

SELECT a.PATIENT, b.CHART

FROM DNT.PATIENT a

Left outer join

Work.test b

where a.patient = b.patient;

RUN;

Don't forget the WHERE condition to join the two tables by a key. If the PATIENT table is large then this may cause performance problems. Other strategies can be used to deal with this.

using PROC sql with 2 tables from two different sources

Hima — Wed, 15 Feb 2012 20:50:13 GMT

First verify if the table "test" exist in work.

Code:

PROC SQL;

select libname, memname, memtype from DICTIONARY.MEMBERS

where libname = 'WORK' AND memname = 'TEST';

QUIT;

Output:

Library Member

Name Member Name Type

ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ

WORK TEST DATA

If the table exists the code provided by SASKiwi should work given the primary key and foreign key relationship exist between the two tables.

using PROC sql with 2 tables from two different sources

teg_76 — Thu, 16 Feb 2012 00:15:08 GMT

Thank you SASKiwi and Hima! You're answers allowed me to get it to work!

using PROC sql with 2 tables from two different sources

Doc_Duke — Thu, 16 Feb 2012 04:09:02 GMT

Teg,

The solutions do work,but there are caveats. If you have a SAS dataset and an Oracle table to join, the solution above will have all of the computation work done on the SAS server. If the data sets are reasonable size, that is not particularly an issue, but if the Oracle tables are huge, there can be a significant performance hit over uploading the SAS data set as a temporary Oracle table and processing on the Oracle server.

As they say, "your mileage may vary".

Doc Muhlbaier

Duke

using PROC sql with 2 tables from two different sources

teg_76 — Thu, 16 Feb 2012 16:35:12 GMT

Hi Doc,

Thanks for your reply. Do you have any suggestions on how I should handle this in the future to better performance?

Thanks!