DATA Step, Macro, Functions and more

multiple left joins

Reply
Super Contributor
Posts: 271

multiple left joins

[ Edited ]

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; 

 

PROC Star
Posts: 325

Re: multiple left joins

Posted in reply to SAS_inquisitive

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;

 

Ask a Question
Discussion stats
  • 1 reply
  • 195 views
  • 0 likes
  • 2 in conversation