Left Join in a Create Table

[anweinbe]

Hi all,

I am trying to build a new table using a proc sql create command (below). This code does work, however I am trying to add to it and have not gotten it to work. I would like to keep all records in my table "a" even if there is no match in table "b". I tried multiple things but clearly missing something easy...

my current code:

proc sql;
create table temp.final_Indp_Prct
as select a.*, b.*
from temp.Summ_Owners_2 as a, temp.Summ_Ind as b
where a.cusip=b.cusip and a.base_year=b.base_year;
quit;

[ed_sas_member · Accepted solution]

Hi @anweinbe 

You can try this (untested code)

proc sql;
	create table temp.final_Indp_Prct as
	select a.*, b.*
	from temp.Summ_Owners_2 as a left join temp.Summ_Ind as b
	on a.cusip=b.cusip and a.base_year=b.base_year;
quit;

-> replace "where" by "on" when you use a left join.

Best,

[anweinbe]

Thank you!