topic Re: Left join only 1 ID in SAS Procedures

Left join only 1 ID

Jay_Aguilar — Mon, 04 May 2020 10:04:52 GMT

Hello everyone,

I have a question regarding a left join. I have two tables which look like this:

table1:

table2:

I now want to join the information from table 2 to my first table but I would only need info3 once so that my final table looks like this:

desired table:

By simply coding this:

proc sql; 
create table test as 
select a.*, b.info3
from table1 as a
left join table2 as b
on a.ID=b.ID;
quit;

I get several duplicate rows. I guess I have to distinct the join somewhere, but I am not sure how.

Thank you for your help.

ed_sas_member — Mon, 04 May 2020 10:12:52 GMT

Try this:

proc sql; 
	create table test as
	select a.*, b.info3
	from table1 as a
	 	 left join
	    (select distinct * from table2) as b
	on a.ID=b.ID;
quit;

Best,

Jay_Aguilar — Mon, 04 May 2020 10:18:13 GMT

Thank you very much for your help!!

ed_sas_member — Mon, 04 May 2020 10:25:58 GMT

You're welcome @Jay_Aguilar 😊