topic Re: Problem with MERGE in SAS Programming

Problem with MERGE

romain — Fri, 03 Jun 2011 13:32:00 GMT

Hi everybody,

When I'm merging two data sets A and B by a common variable ID, if there are two rows entirely filled for an ID in A and one row partially filled for the same ID in B, after the merge I obtain 2 rows entirely filled (missing values in B are replaced by A values).
The problem is I would like to keep an empty field, of course.

Here is my code :

**********************************************************
DATA tmp;
MERGE
tmp_2008 (in=a keep=id dat_2008)
tmp_2009 (in=b keep=id dat_2009)
BY id;
run;
**********************************************************

And the result :

id----------------dat_2008----------------------dat_2009----------

33882041 -- 16MAR2011:18:27:21 -- 17MAR2011:17:40:03
33882041 -- 16MAR2011:18:27:21 -- 22MAR2011:18:06:49

(Before merging, I have two rows in dat_2009, one in dat_2008 and after merging these data sets I don't want the duplicate value in the second row for dat_2008 but keep the field empty).

Hope to make myself clear,

Thanks a lot 🙂

Re: Problem with MERGE

sbb — Fri, 03 Jun 2011 14:27:17 GMT

You will need to use different SAS variable names and populate your KEEP variable(s) in your DATA step based on the IN= variable conditions. What behavior you are seeing is the SAS architecture standard with MERGE processing -- any other desired result requires the use of alternate-named SAS variables and your own tests for missing/blank value logic.

Scott Barry
SBBWorks, Inc.

Re: Problem with MERGE

data_null__ — Fri, 03 Jun 2011 14:45:58 GMT

This appears to work with your example data. Test it and see for yourself.

[pre]
data tmp_2008;
input id $ dat_2008:datetime.;
format dat_: datetime.;
cards;
33882041 16MAR2011:18:27:21
run;
data tmp_2009;
input id $ dat_2009:datetime.;
format dat_: datetime.;
cards;
33882041 17MAR2011:17:40:03
33882041 22MAR2011:18:06:49
run;

DATA tmp;
MERGE
tmp_2008 (in=a keep=id dat_2008)
tmp_2009 (in=b keep=id dat_2009)
;
BY id;
output;
call missing(of dat_:);
run;
proc print;
run;
[/pre] Message was edited by: data _null_;

Re: Problem with MERGE

romain — Fri, 03 Jun 2011 14:56:01 GMT

I was trying to do what sbb said.

Your solution works perfectly data _null_;

Thank you both.