Hi there -
I am trying to merge two dataset by id, but one data has unique id with multiple observations and the second data has unique id with other information that I need. I want to merge Data2 info to Data1 for all the observations.
Data 1
id name score
1 Jo 90
1 Jo 86
1 Jo 54
2 Julia 87
2 Julia 89
Data 2
id Age Sex
1 3 M
2 5 F
Outcome file that I want to see
id name score Age Sex
1 Jo 90 3 M
1 Jo 86 3 M
1 Jo 54 3 M
2 Julia 87 5 F
2 Julia 89 5 F
I ran the code below but it seems that it merged with the first observation only.. What am I missing? Thank you.
data outcome;
merge Data1 Data2;
by id;
run;
data want;
merge data1 (in=d1) data2 (in=d2);
by id;
if d1 & d2;
run;
You can use the IN option which creates an indicator variable to indicate a record source.Then if it's from both you keep only those. The join type is considered an 'inner join' in SQL.
There is huge difference between <<D1 & D2>> which means <<D1 and D2>> and <<D1&D2>> which has a reference to a macro variable named D2.
When we supply code, we do so in a way that allows direct copy/pasting to your SAS environment (we post the code usein g the "little running man" button). You MUST do this first, to get the tested code (code supplied by senior posters here has been tested in their environment, unless noted otherwise) as-is; after that, you can play around with it and see what consequences each change that you introduce has.
Double check your data2 set to make sure that there are no duplicates of ID. That may also lead to unexpected results.
You don't actually show what you mean, but I suspect that the issue is that the NEW variables from the ONE dataset that you are trying to add to the MANY dataset already exist. In that case SAS will overwrite the value read from the ONE dataset for the first observation in the group with the values read from the MANY dataset for the 2,3, etc variables in the group. You could use the DROP= dataset option.
data outcome;
merge Data1(drop=age sex) Data2;
by id;
run;
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: