topic Re: Merge - One-to-Many Problems in SAS Programming

Merge - One-to-Many Problems

_maldini_ — Sat, 30 Jul 2016 01:10:26 GMT

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!

Re: Merge - One-to-Many Problems

Reeza — Sat, 30 Jul 2016 01:21:29 GMT

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

Re: Merge - One-to-Many Problems

PGStats — Sat, 30 Jul 2016 02:40:44 GMT

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;

Re: Merge - One-to-Many Problems

LinusH — Sat, 30 Jul 2016 02:45:58 GMT

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.

Re: Merge - One-to-Many Problems

Ksharp — Sat, 30 Jul 2016 05:01:03 GMT

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;

Re: Merge - One-to-Many Problems

Cynthia_sas — Sat, 30 Jul 2016 16:29:31 GMT

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

Re: Merge - One-to-Many Problems

Tom — Sat, 30 Jul 2016 19:30:05 GMT

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;

Re: Merge - One-to-Many Problems

_maldini_ — Mon, 01 Aug 2016 23:04:21 GMT

@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.

Re: Merge - One-to-Many Problems

Reeza — Mon, 01 Aug 2016 23:06:34 GMT

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.

Re: Merge - One-to-Many Problems

Tom — Mon, 01 Aug 2016 23:13:47 GMT

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;

Re: Merge - One-to-Many Problems

_maldini_ — Tue, 02 Aug 2016 15:32:08 GMT

@Reeza

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

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!

Re: Merge - One-to-Many Problems

Reeza — Tue, 02 Aug 2016 18:17:30 GMT

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.