topic Re: Left Join in a Create Table in SAS Procedures

Left Join in a Create Table

anweinbe — Sun, 15 Dec 2019 17:20:14 GMT

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;

Re: Left Join in a Create Table

ed_sas_member — Sun, 15 Dec 2019 17:40:26 GMT

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,

Re: Left Join in a Create Table

anweinbe — Sun, 15 Dec 2019 19:20:03 GMT

Thank you!