DATA Step, Macro, Functions and more

Merge/ Combine

Accepted Solution Solved
Reply
Contributor
Posts: 71
Accepted Solution

Merge/ Combine

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 !


Accepted Solutions
Solution
‎11-11-2016 03:00 AM
Contributor
Posts: 71

Re: Merge/ Combine

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

View solution in original post


All Replies
Super User
Posts: 19,768

Re: Merge/ Combine

Check your input datasets. One has duplicates. 

 

Contributor
Posts: 71

Re: Merge/ Combine

Yes, Dataset2 has duplicates. In my output instead of 10 records, I am getting far more than 10.
Super User
Posts: 19,768

Re: Merge/ Combine

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. 

Super User
Posts: 5,424

Re: Merge/ Combine

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
Contributor
Posts: 71

Re: Merge/ Combine

[ Edited ]

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.

Super User
Posts: 19,768

Re: Merge/ Combine

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; 

Solution
‎11-11-2016 03:00 AM
Contributor
Posts: 71

Re: Merge/ Combine

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

Super User
Posts: 19,768

Re: Merge/ Combine

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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