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?
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.
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.
It was a one-to-one merge.
Thanks!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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: