Hi All,
I would like two combine the following two datasets.
Dataset-1 | ||||
ID | P1 | P2 | P3 | P4 |
1 | A | B | C | D |
2 | B | A | C | |
3 | A | C | B | D |
4 | D | C | A | B |
5 | C | A | B | |
6 | C | A | B | D |
7 | A | B | C | |
8 | A | C | B | |
9 | A | C | ||
10 | B | C | D | A |
Dataset-2 | |
Main_Id | ID |
1 | 2 |
2 | 3 |
3 | 2 |
4 | 2 |
5 | 2 |
6 | 5 |
7 | 5 |
8 | 9 |
9 | 9 |
10 | 9 |
I expect the following Dataset
Main_Id | ID | P1 | P2 | P3 | P4 |
1 | 2 | B | A | C | |
2 | 3 | A | C | B | D |
3 | 2 | B | A | C | |
4 | 2 | B | A | C | |
5 | 2 | B | A | C | |
6 | 5 | C | A | B | |
7 | 5 | C | A | B | |
8 | 9 | A | C | ||
9 | 9 | A | C | ||
10 | 9 | A | C |
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 !
Thanks Everbody. I have found the solution in proc sql. Left join is giving me desired output.
Check your input datasets. One has duplicates.
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.
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.
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;
Thanks Everbody. I have found the solution in proc sql. Left join is giving me desired output.
As long as you figured out why it happened. If duplicates in Table1 by ID aren't identical you may have unexpected results.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.