BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jay_Aguilar
Calcite | Level 5

Hello everyone,

 

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

table1:

IDinfo1 info2
1xyz10
2abc20
3def30
4ghi40

 

table2:

IDinfo3
1A
1A
2C
2C
2C
3A
3A
4F
4F
4F

 

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:

IDinfo1 info2info3
1xyz10A
2abc20C
3def30A
4ghi40F

 

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
ed_sas_member
Meteorite | Level 14

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,

 

View solution in original post

3 REPLIES 3
ed_sas_member
Meteorite | Level 14

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
Calcite | Level 5

Thank you very much for your help!! 

ed_sas_member
Meteorite | Level 14

You're welcome @Jay_Aguilar 😊

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 820 views
  • 0 likes
  • 2 in conversation