multiple left join

Accepted Solution Solved
Reply
Contributor
Posts: 71
Accepted Solution

multiple left join

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 ;

Accepted Solutions
Solution
3 weeks ago
Super User
Super User
Posts: 9,799

Re: multiple left join

[ Edited ]

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


All Replies
Solution
3 weeks ago
Super User
Super User
Posts: 9,799

Re: multiple left join

[ Edited ]

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.

Contributor
Posts: 71

Re: multiple left join

 

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

Super User
Super User
Posts: 9,799

Re: multiple left join

[ Edited ]

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. 

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 70 views
  • 1 like
  • 2 in conversation