topic Merge step overwriting shared vars? in SAS Programming

Merge step overwriting shared vars?

Walternate — Thu, 30 Jun 2016 18:03:27 GMT

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

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?

Re: Merge step overwriting shared vars?

Astounding — Thu, 30 Jun 2016 18:51:18 GMT

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.

Re: Merge step overwriting shared vars?

Walternate — Thu, 30 Jun 2016 19:04:00 GMT

It was a one-to-one merge.

Thanks!