DATA Step, Macro, Functions and more

Merge step overwriting shared vars?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 138
Accepted Solution

Merge step overwriting shared vars?

Hi,

 

I have two datasets that both have ID and a number of other variables. Some of these variables exist on both datasets. I sorted both of them by ID and merged, and I thought I had dropped all vars from Dataset 1 that also existed in Dataset 2 (I wanted the values from Dataset 2 of those variables), however, there is a variable I forgot to drop.

 

Is there a rule for merges in terms of which dataset would overwrite which if two merging datasets share a variable (but not the variable that is being merged on).

 

Dataset 1

ID   Shared_var

1           abc

2

 

Dataset 2

ID     Shared_var

1          def

2         xyz

 

ie, if I sort both and merge them on ID only, which values of shared_var would appear in the output dataset? 


Accepted Solutions
Solution
‎06-30-2016 03:04 PM
Super User
Posts: 5,497

Re: Merge step overwriting shared vars?

Posted in reply to Walternate

There is a rule:  whichever value was read last.  But that rule is simple only when the merge is one-to-one.  In that case, the value you get depends on the order in the MERGE statement:

 

merge a b;

by id;

 

The value of common variables (for a one-to-one merge) comes from data set B.  SAS reads a value from data set A, then reads a value from data set B.  The value from B is read last, and overwrites the value read from data set A.

 

If there is a mismatch, and an ID appears only in data set A but not in data set B, the value will be the one found in data set A.

 

For a many-to-one merge, the situation is more complex.  SAS never reads the same observation twice.  It reads each observation just once, and retains what it has read.  If that's the situation you're facing, I can go into more detail.

View solution in original post


All Replies
Solution
‎06-30-2016 03:04 PM
Super User
Posts: 5,497

Re: Merge step overwriting shared vars?

Posted in reply to Walternate

There is a rule:  whichever value was read last.  But that rule is simple only when the merge is one-to-one.  In that case, the value you get depends on the order in the MERGE statement:

 

merge a b;

by id;

 

The value of common variables (for a one-to-one merge) comes from data set B.  SAS reads a value from data set A, then reads a value from data set B.  The value from B is read last, and overwrites the value read from data set A.

 

If there is a mismatch, and an ID appears only in data set A but not in data set B, the value will be the one found in data set A.

 

For a many-to-one merge, the situation is more complex.  SAS never reads the same observation twice.  It reads each observation just once, and retains what it has read.  If that's the situation you're facing, I can go into more detail.

Frequent Contributor
Posts: 138

Re: Merge step overwriting shared vars?

Posted in reply to Astounding

It was a one-to-one merge. 

 

Thanks!

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 1445 views
  • 0 likes
  • 2 in conversation