multiple left join

[manya92]

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 ;

[RW9 · Accepted solution]

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]

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]

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]

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]

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]

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]

Another nice thing about merge is that the code is succinct i.e. it requires a lot less typing than proc sql code.