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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Shmuel
Garnet | Level 18

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

6 REPLIES 6
Shmuel
Garnet | Level 18

Your conclusion is right:

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

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=.
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
PGStats
Opal | Level 21

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
Mandy1
Fluorite | Level 6

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!

PGStats
Opal | Level 21

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
Mandy1
Fluorite | Level 6

Thank you all for your help with this question!

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
  • 6 replies
  • 5209 views
  • 2 likes
  • 4 in conversation