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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.