Hello sir, I have a question regarding the merge statement. As an example, I try to merge two files data01 and data02 into a single file data03, by variable a. But the values of data02 overwrite the values of data01. For missing values of data01, this is good. But it is bad that the missing values of data02 also overwrite the values of data01.
My question is how to keep values of data01 from being overwritten if they are not missing?
data work.data01;
input a b;
cards;
1 2
2 .
3 1;
run;
data work.data02;
input a b;
cards;
1 2
2 2
3 . ;
run;
data work.data03;
merge work.data01 work.data02;
by a;
run;
/*Result:
Obs a b
1 1 2
2 2 2
3 3 .
Look into the coalescec function and doing a sql join instead of merge.
http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a002518268.htm
thanks for your instruction.
Hi
You can also use the RENAME data set options in one of the two datasets like following
data work.data01;
input a b;
cards;
1 2
2 .
3 1;
run;
data work.data02;
input a b;
cards;
1 2
2 2
3 . ;
run;
data work.data03;
merge work.data01 work.data02 (rename = (b = b1));
by a;
run;
I don't understand why you don't want 3 1 for the last record. If you really did want that result, you could just use the update statement in a datastep. e.g.,
data work.data03;
update work.data01 work.data02;
by a;
run;
The update statement is also a good idea. I often overlook the update and modify routines as there usefulness in my current tasks is limited.
Hello Ruth,
Try the code below. If you include both variables in the by statement they will tend to interleave and not just overwrite and then you can delete the extra b rows that are blank with the subsetting if statement.
data data03;
merge data01 data02;
by a b;
if b;
run;
Hope this helps,
Rich
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: