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!
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;
They won't be identical duplicates. What's your filtering criteria?
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;
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;
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;
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.
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.
<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!
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.