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 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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