topic Re: merge two data sets by conditionally in SAS Programming

merge two data sets by conditionally

Mandy1 — Sat, 11 Nov 2017 19:21:42 GMT

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

Re: merge two data sets by conditionally

Shmuel — Sat, 11 Nov 2017 20:09:50 GMT

Your conclusion is right:

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

Re: merge two data sets by conditionally

mkeintz — Sun, 12 Nov 2017 00:02:08 GMT

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:

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

Re: merge two data sets by conditionally

PGStats — Sun, 12 Nov 2017 03:46:19 GMT

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;

Re: merge two data sets by conditionally

Mandy1 — Sun, 12 Nov 2017 15:38:02 GMT

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!

Re: merge two data sets by conditionally

PGStats — Sun, 12 Nov 2017 23:39:42 GMT

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

Re: merge two data sets by conditionally

Mandy1 — Thu, 16 Nov 2017 13:12:55 GMT

Thank you all for your help with this question!