Left join only 1 ID

[Jay_Aguilar]

Hello everyone,

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

table1:

ID	info1	info2
1	xyz	10
2	abc	20
3	def	30
4	ghi	40

table2:

ID	info3
1	A
1	A
2	C
2	C
2	C
3	A
3	A
4	F
4	F
4	F

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:

ID	info1	info2	info3
1	xyz	10	A
2	abc	20	C
3	def	30	A
4	ghi	40	F

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 · Accepted solution]

Hi @Jay_Aguilar 

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]

Thank you very much for your help!! 

[ed_sas_member]

You're welcome @Jay_Aguilar 😊