BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
teg_76
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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

5 REPLIES 5
SASKiwi
PROC Star

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
Obsidian | Level 7

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
Calcite | Level 5

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

Doc_Duke
Rhodochrosite | Level 12

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
Calcite | Level 5

Hi Doc,

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

Thanks!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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