BookmarkSubscribeRSS Feed
romain
Calcite | Level 5
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 🙂
3 REPLIES 3
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
data_null__
Jade | Level 19
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_;
romain
Calcite | Level 5
I was trying to do what sbb said.

Your solution works perfectly data _null_;

Thank you both.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 3 replies
  • 984 views
  • 0 likes
  • 3 in conversation