merge two data sets by conditionally

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

merge two data sets by conditionally

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


Accepted Solutions
Solution
‎11-18-2017 02:16 PM
Trusted Advisor
Posts: 1,837

Re: merge two data sets by conditionally

Your conclusion is right:

 merge in a few steps, like merge non missing id2  first and merge missing id2 at the second step.

View solution in original post


All Replies
Solution
‎11-18-2017 02:16 PM
Trusted Advisor
Posts: 1,837

Re: merge two data sets by conditionally

Your conclusion is right:

 merge in a few steps, like merge non missing id2  first and merge missing id2 at the second step.
Trusted Advisor
Posts: 1,345

Re: merge two data sets by conditionally

[ Edited ]

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:

 

  1. I assume that both data sets are sorted by ID1/ID2.
  2. For each ID1, data set B has either the same sequence of ID2 value as A, or it has a single observations with ID2=.
Esteemed Advisor
Posts: 5,540

Re: merge two data sets by conditionally

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;
PG
Occasional Contributor
Posts: 9

Re: merge two data sets by conditionally

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!

Esteemed Advisor
Posts: 5,540

Re: merge two data sets by conditionally

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 Smiley Happy

PG
Occasional Contributor
Posts: 9

Re: merge two data sets by conditionally

Thank you all for your help with this question!

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 239 views
  • 2 likes
  • 4 in conversation