using PROC sql with 2 tables from two different sources

[teg_76]

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;

[SASKiwi · Accepted solution]

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.

[Hima]

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.

[teg_76]

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

[Doc_Duke]

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

[teg_76]

Hi Doc,

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

Thanks!