Hello,
I would like to merge the following two data sets (data set A and B) where each row is uniquely identified by id1 and id2 both in each data set (no duplicates). The merge is done by variables id1 and id2 but with some additional condition:
if id2 is not missing in data set B, then merge by two varaibles id1 and id2 both;
if id2 is missing in data set B, then merge by id1 only (i.e., when there are multiple rows in data set A, one row in B is merged to multiple rows in A, such as id1=4)
The issue is the missing values for id2 in data set B.
I wondered if you have any clean way to do this merge? What I can come up is to do the merge in a few steps, like merge non missing id2 first and merge missing id2 at the second step. Thank you for your time!
Mandy1
*************Data set A************
id1 id2 wage
1 101 1000
2 302 2000
2 305 3000
3 400 5000
4 500 5000
4 502 6000
*************Data set B************
id1 id2 hours
1 . 40
2 302 20
2 305 40
3 400 35
4 . 40
*************desired resulted data set************
id1 id2 wage hours
1 101 1000 40
2 302 2000 20
2 305 3000 40
3 400 5000 35
4 500 5000 40
4 502 6000 40
Your conclusion is right:
merge in a few steps, like merge non missing id2 first and merge missing id2 at the second step.
Your conclusion is right:
merge in a few steps, like merge non missing id2 first and merge missing id2 at the second step.
You could do two data step, each merging A against a subset of B.
Editted note: Actually, given the assumptions noted below, you can do a single step merge, by ignoring ID2 in data set B:
data want;
merge a b (drop=id2);
by id1;
run;
But, with two SET statements, it is possible to do this in a single data step:
data a;
input id1 id2 wage;
datalines;
1 101 1000
2 302 2000
2 305 3000
3 400 5000
4 500 5000
4 502 6000
run;
data b;
input id1 id2 hours;
datalines;
1 . 40
2 302 20
2 305 40
3 400 35
4 . 40
run;
data want (drop=_:);
set b (rename=(id2=_id2b));
do until (id2=_id2b or last.id1=1);
set a;
by id1;
output;
end;
run;
Note:
Can also be done with SQL:
proc sql;
create table C as
select
a.id1,
a.id2,
wage,
hours
from a inner join b on
a.id1=b.id1 and (a.id2=b.id2 or b.id2 is missing);
quit;
Thank you all for the above responses!
For the SQL way, based on my experience in the past I encountered the following issue:
When the "join on " statement in SQL contains a "or" compared to all using "and," the "join" process takes much much longer to complete. Once I remember clearly that it seemed like it will take forever; then I gave up and broke that "join" into two "join" so there is no "or" in the "join" process. That is much quicker.
Has anyone noticed the same issue? Why join on containing an "or" takes longer?
Thank you!
True. Other users have mentioned it as well. I always hesitate before using an OR condition in SAS/SQL. It seems to be a weakness of SAS/SQL. But then, I never wrote an SQL interpreter myself
Thank you all for your help with this question!
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.