DATA Step, Macro, Functions and more

Merge - One-to-Many Problems

Accepted Solution Solved
Reply
Regular Contributor
Posts: 199
Accepted Solution

Merge - One-to-Many Problems

I am trying to merge 2 datasets using a by (i.e. ID) variable. SAS performs a one-to-many merge, which results in repeated identical observations. I only want unique observations in the final dataset. 

 

For example, in dataset one, there are 5 observations for ID=23. In dataset two, there are 2 observations for ID=23. In the final merged dataset, there are 5 observations total for ID=23, but only 2 of them are unique. The other 3 are duplicates of the 2nd / last observation in the second dataset. 

 

Thanks for the help!


Accepted Solutions
Solution
‎08-03-2016 04:40 PM
Super User
Super User
Posts: 6,495

Re: Merge - One-to-Many Problems

Let's call the dataset that you want to control the number of observations MASTER.

You can use the IN= dataset option, but you need to reset it so that it doesn't continue to be true after the MASTER dataset runs out of observations.

 

data want ;
   merge master (in=in1) other ;
   by id;
   if in1 then output;
   in1=0;
run;

View solution in original post


All Replies
Super User
Posts: 17,744

Re: Merge - One-to-Many Problems

They won't be identical duplicates. What's your filtering criteria? 

Respected Advisor
Posts: 4,640

Re: Merge - One-to-Many Problems

Compare:

data a;
input id x$;
datalines;
23 a
23 b
23 c
23 d
23 e
;
data b;
input id y$;
datalines;
23 aa
23 bb
;

data c;
merge a b;
by id;
run;

title "Merge";
proc print; run;

data d;
set a;
set b;
by id;
run;

title "Set Set";
proc print; run;
PG
Super User
Posts: 5,254

Re: Merge - One-to-Many Problems

You can't do 1 to many merge if you don't have unique I'd in one data set.
The merge style you are describing is kinda dangerous leaving matching to chance. How do you know which of the two records should be match to which two records in the other data set?
To me it sounds like that you need to identify another variable that can expand your merging key.
Data never sleeps
Super User
Posts: 9,662

Re: Merge - One-to-Many Problems

You need a special MERGE .



data a;
input id x$;
datalines;
23 a
23 b
23 c
23 d
23 e
;
data b;
input id y$;
datalines;
23 aa
23 bb
;

data c;
ina=0;inb=0;
merge a(in=ina) b(in=inb);
by id;
if ina and inb;
run;


SAS Super FREQ
Posts: 8,739

Re: Merge - One-to-Many Problems

Hi:
But even with the "special" code, where the in variables are set to 0, this is a NOTE that should be dealt with the correct way:
NOTE: MERGE statement has more than one data set with repeats of BY values.

SAS is not "happy" about duplicate BY values in the datasets being merged. The OP is not doing a one to many merge, the OP is trying to do a many-to-many merge, which might produce undesired results. As Reeza and LinusH suggested, your choices to really get a one-to-many merge will be to reduce the observations in one dataset or the other so you have a unique value (1 obs) for ID in one of the files OR to find another variable that makes the merge BY variable combination result in a one to many merge.

cynthia
Super User
Super User
Posts: 6,495

Re: Merge - One-to-Many Problems

What SAS is doing is that when one dataset runs out of observations for a BY group it just keeps the values of the variables contributed from that dataset.  So in your 5 to 2 match the last four observations will have the exact same values for the variables contributed by the dataset that only had 2 observations.

 

So what do you want to happen?  

 

If you just want one observation per BY group you could just add a subsetting if statement. 

data want ;
   merge data2 data5 ;
   by id;
   if first.id ;
run;

If you want to prevent the values from the short stack getting carried forward then you could try setting them to missing.  You would still have 5 observations, but the last three will ahve missing values for the variables from the short dataset.

data want ;
  merge data2 data5 ;
  by id;
  output;
  call missing(of _all_);
run;

If you want every observation from one matched to every observation in the other then just an SQL full join instead. That way instead of 5 observations you will get 10.

proc sql;
  create table want as 
  select * 
  from data2
  full join data5
  on data2.id = data5.id
 ;
quit;

 

Regular Contributor
Posts: 199

Re: Merge - One-to-Many Problems

@Tom

 

Thanks for your clear and detailed explanation here. Unfortunately, I am still struggling to solve this problem.

 

In some situations, the short stack is dataset two, in other situations, the short stack is dataset one (I should have made this clear in the original post).

 

Dataset one will never have MORE than 5 observations per BY group variable, but it could have LESS (Some participants dropped out).

 

Dataset two may have more, or less, than 5 observations. 

 

I want as many observations per BY group as their are in dataset two. For example, if there are 3 observations in dataset two, then I want the variables from dataset one repeated for each of the 3 observations in dataset two. If there are 15 observations in dataset two, then I want the variables from dataset one repeated for each of the 15 observations. 

 

Does this make sense? 

 

I want dataset two to determine the final number of observations in the joined dataset.

 

Thanks again for your assistance.

Super User
Posts: 17,744

Re: Merge - One-to-Many Problems

Someone said it earlier. You need to add variables to your filter. If the rows are not exact duplicates which variables differentiate them? Those are the variables you use to create your additional filters. 

 

Regular Contributor
Posts: 199

Re: Merge - One-to-Many Problems

@Reeza

 

<You need to add variables to your filter.>

 

I'm not clear on what you mean by "filter". Do you mean add additional variables to the BY statement?

 

<If the rows are not exact duplicates which variables differentiate them?>

 

The rows are exact duplicates. In the attached image, the last 3 rows are identical. I only want one of them in the final dataset.

 

Thanks for your help!


sas communities.jpg
Super User
Posts: 17,744

Re: Merge - One-to-Many Problems

Are your records unique in Table A and in Table B?

If not, make them unique in each of the tables first BEFORE the join.

Solution
‎08-03-2016 04:40 PM
Super User
Super User
Posts: 6,495

Re: Merge - One-to-Many Problems

Let's call the dataset that you want to control the number of observations MASTER.

You can use the IN= dataset option, but you need to reset it so that it doesn't continue to be true after the MASTER dataset runs out of observations.

 

data want ;
   merge master (in=in1) other ;
   by id;
   if in1 then output;
   in1=0;
run;
☑ This topic is SOLVED.

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

Discussion stats
  • 11 replies
  • 1278 views
  • 12 likes
  • 7 in conversation