Help using Base SAS procedures

using PROC sql with 2 tables from two different sources

Accepted Solution Solved
Reply
Contributor
Posts: 41
Accepted Solution

using PROC sql with 2 tables from two different sources

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;


Accepted Solutions
Solution
‎02-15-2012 02:18 PM
Super User
Posts: 3,105

using PROC sql with 2 tables from two different sources

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.

View solution in original post


All Replies
Solution
‎02-15-2012 02:18 PM
Super User
Posts: 3,105

using PROC sql with 2 tables from two different sources

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.

Regular Contributor
Posts: 233

using PROC sql with 2 tables from two different sources

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.

Contributor
Posts: 41

using PROC sql with 2 tables from two different sources

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

Trusted Advisor
Posts: 2,113

using PROC sql with 2 tables from two different sources

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

Contributor
Posts: 41

using PROC sql with 2 tables from two different sources

Hi Doc,

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

Thanks!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 166 views
  • 3 likes
  • 4 in conversation