multiple left joins

SAS_inquisitive — Fri, 07 Jul 2017 16:27:57 GMT

Hello,

In this program, the final table is not getting any records from table z as there is matching column in table y and z (id2)? I know intermediate table from first left join is not picking id2 = 12 record. How do include second record from table y so it can be joined with table z?

data x;
	input id1 var1 $;
	cards;
1  a
2  b
;
run;

data y;
	input id1 id2 var2 $;
	cards;
1 11 c
3 12 d
;
run;

data z;
	input id2 var3 $;
	cards;
12 e
;
run;

proc sql noprint;
	create table want as
		select	a.var1,
			b.var2,
			c.var3
		from x as a
			left join y as b 
				on a.id1 = b.id1  
			left join z as c 
				on b.id2 = c.id2;
quit;

Re: multiple left joins

kiranv_ — Fri, 07 Jul 2017 16:41:37 GMT

you need to do a full join in the begining , something like this

proc sql noprint;

create table want as

select a.var1,

b.var2,

c.var3

from x as a

full join y as b

on a.id1 = b.id1

inner join z as c

on b.id2 = c.id2;

quit;

topic multiple left joins in SAS Programming

multiple left joins

Re: multiple left joins