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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

11 REPLIES 11
Reeza
Super User

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

PGStats
Opal | Level 21

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
LinusH
Tourmaline | Level 20
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
Ksharp
Super User
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;


Cynthia_sas
SAS Super FREQ
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
Tom
Super User Tom
Super User

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;

 

_maldini_
Barite | Level 11

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

Reeza
Super User

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. 

 

_maldini_
Barite | Level 11

@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
Reeza
Super User

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.

Tom
Super User Tom
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 11 replies
  • 42968 views
  • 14 likes
  • 7 in conversation