BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
manya92
Fluorite | Level 6

Hi all, I want to know how can join 2 tables to one tables by using left join. I have my patient cohort and want to join 2 other datasets to get more variables. The number of observations will remain the same just the variable number will increase. 

 

My code looks like this.. but it is incomplete. Is there a way I can use multiple left joins ?

Is there a way to combine both the dataset ? 

 

PS. b.code in both the steps have different values for code, one is coming from dataset1 and the other is coming from dataset2.

 

Thanks

 

/*LEFT JOIN TO GET CODES FROM DATASET1 */
proc sql ;
	create table _01_esrd_dial_codes as 
	select distinct a.*, b.code 
	from _01_pt as a
	left join DATASET1 as b 
	on a.enrolid = b.enrolid ;
quit ;

/*LEFT JOIN TO GET CODES FROM DATASET2 */
proc sql ;
	create table _01_JOIN2 as 
	select distinct a.*, b.code 
	from _01_pt as a
	left join DATASET2 as b 
	on a.enrolid = b.enrolid ;
quit ;
1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Something like:

proc sql ;
	create table _01_esrd_dial_codes as 
	select distinct a.*, b.code,c.code as c_code
	from _01_pt as a
	left join dataset1 as b 
	on a.enrolid = b.enrolid
        left join dataset2 as c
        on a.enrolid=c.enrolid;
quit;

You can keep adding left or right of full joins on as much as you like up to about 20 something joins.

View solution in original post

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Something like:

proc sql ;
	create table _01_esrd_dial_codes as 
	select distinct a.*, b.code,c.code as c_code
	from _01_pt as a
	left join dataset1 as b 
	on a.enrolid = b.enrolid
        left join dataset2 as c
        on a.enrolid=c.enrolid;
quit;

You can keep adding left or right of full joins on as much as you like up to about 20 something joins.

manya92
Fluorite | Level 6

 

This works but i also want to stack b.code and c_code. Is that possible in the same step as the left join 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Yes.  I would do it like this, just clearer for me:

proc sql ;
  create table _01_esrd_dial_codes as 
  select distinct a.*, 
b.code from _01_pt as a left join (select enrolid,
code
from dataset1 union all
select enrolid,
code
from dataset2) b
on a.enrolid=b.enrolid; quit;

Note that this assumes the same type in both dataset1 and 2. 

flcong
Calcite | Level 5
I have a question regarding this piece of code. I find that if the datasets are large, combining two left joins in this way is much slower than separating them into two proc sqls. Does SAS form a Cartesian product of all three data sets, `_01_pt`, `dataset1`, and `dataset2` when executing this query?
RW9
Diamond | Level 26 RW9
Diamond | Level 26

If your datasets are large, then there is a good case for not using SQL at all.  SAS implementation of SQL (from what I recal) is that it is all done be loading and writing into intermediary tables, so when data gets larger then it takes longer to process.

The question raised here was "how do I do xyz", not should I be doing "xyz".  IN case of big datasets I would set both sets of data together with a category defining which dataset of orign, then merge the two sets of data using a datastep merge, this would be the most efficient method to get the final result.

flcong
Calcite | Level 5
Thank you! I wasn't aware of the performance advantage of data merge over sql. I basically only use sql, for convenience. No wonder I feel SAS is slow.
jjsingh04
Obsidian | Level 7
Another nice thing about merge is that the code is succinct i.e. it requires a lot less typing than proc sql code.
Our lives are enriched by the people around us.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 7 replies
  • 49424 views
  • 2 likes
  • 4 in conversation