BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Walternate
Obsidian | Level 7

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? 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

2 REPLIES 2
Astounding
PROC Star

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.

Walternate
Obsidian | Level 7

It was a one-to-one merge. 

 

Thanks!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 12924 views
  • 2 likes
  • 2 in conversation