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.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1442 views
  • 0 likes
  • 3 in conversation