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

Hi All,

 

I would like two combine the following two datasets.

Dataset-1   
IDP1P2P3P4
1ABCD
2BAC 
3ACBD
4DCAB
5CAB 
6CABD
7ABC 
8ACB 
9AC  
10BCDA

 

Dataset-2
Main_IdID
12
23
32
42
52
65
75
89
99
109

 

I expect the following Dataset

 

Main_IdIDP1P2P3P4
12BAC 
23ACBD
32BAC 
42BAC 
52BAC 
65CAB 
75CAB 
89AC  
99AC  
109AC  

 

I wrote following program

 

proc sort dataset1;

by id;

run;

proc sort dataset2;

by id;

run;

data final ;
merge dataset1 dataset2;
by id;
run;

 

but I am getting multiple records in the result. Please advice. Thanks in advance !

1 ACCEPTED SOLUTION

Accepted Solutions
deega
Quartz | Level 8

Thanks Everbody. I have found the solution in proc sql. Left join is giving me desired output.

View solution in original post

8 REPLIES 8
Reeza
Super User

Check your input datasets. One has duplicates. 

 

deega
Quartz | Level 8
Yes, Dataset2 has duplicates. In my output instead of 10 records, I am getting far more than 10.
Reeza
Super User

Oh... you aren't actually joining on ID. 

 

You want to join on ID from dataset one and mainID in dataset 2. You'll need to rename your variables and modify your sort accordingly. 

LinusH
Tourmaline | Level 20
Why are you merging by Id when from your example it's pretty clear that main_ID is the key?
Maybe you elaborate around your real life case and tell what you expect to get from merging on id.
Data never sleeps
deega
Quartz | Level 8

My main Dataset is Dataset-2 and I want to add detail information of corresponding ID. The detail information of Id is in form of P1 P2... etc in Dataset1 and that is why I am merging by ID.

Reeza
Super User

Are you sure dataset 1 doesn't have duplicates? 

 

Otherwise you could try IN to filter but you should first know why it's happening otherwise you may get unexpected results. 

 

Data want; 

merge d1(in=in1) d2(in=in2);

by id;

 

if in2; *cant recall if it's the first or second that filters but hopefully you get the idea - keep only records from d1; 

run; 

deega
Quartz | Level 8

Thanks Everbody. I have found the solution in proc sql. Left join is giving me desired output.

Reeza
Super User

As long as you figured out why it happened. If duplicates in Table1 by ID aren't identical you may have unexpected results. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1111 views
  • 0 likes
  • 3 in conversation