topic Re: one to many merge in SAS Programming

one to many merge

cphd — Tue, 30 Jun 2020 16:55:56 GMT

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;

Re: one to many merge

Reeza — Tue, 30 Jun 2020 17:02:07 GMT

What did the log show? I would expect it to work just as] posted so I suspect your data wasn't sorted and the merge truncated due to an error?

Re: one to many merge

cphd — Tue, 30 Jun 2020 17:30:31 GMT

Hi Reeza, thank you for your response.

It was successfully merged but there were some missing info in the outcome file, which made me confused. I realized that there are additional information that didn't match to each other. For example, I have id = 4 in data 2 but not in data 1.

So... my question now will be How can I get the outcome file with matched sample only?

Re: one to many merge

ballardw — Tue, 30 Jun 2020 17:32:03 GMT

Double check your data2 set to make sure that there are no duplicates of ID. That may also lead to unexpected results.

Re: one to many merge

Reeza — Tue, 30 Jun 2020 17:36:05 GMT

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.

https://blogs.sas.com/content/sastraining/2015/05/27/life-saver-tip-for-comparing-proc-sql-join-with-sas-data-step-merge/

Re: one to many merge

Tom — Tue, 30 Jun 2020 19:50:27 GMT

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;

Re: one to many merge

cphd — Tue, 30 Jun 2020 19:54:48 GMT

I followed your code but the log said

"WARNING:Apparent symbolic reference D2 not resolved. "

And it gave me the same result of the entire merged dataset.

Re: one to many merge

Tom — Tue, 30 Jun 2020 19:59:06 GMT

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.

Re: one to many merge

Reeza — Tue, 30 Jun 2020 20:11:28 GMT

Then you didn't use the code exactly. Post your code and log?
I suspect Tom is right, you didn't have a space after the &.

Re: one to many merge

Kurt_Bremser — Wed, 01 Jul 2020 05:37:46 GMT

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.