topic The merge statement in the data set in SAS Procedures

The merge statement in the data set

Ruth — Tue, 20 Sep 2011 20:34:18 GMT

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 .

The merge statement in the data set

FriedEgg — Tue, 20 Sep 2011 20:51:35 GMT

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

The merge statement in the data set

Ruth — Tue, 20 Sep 2011 21:05:29 GMT

thanks for your instruction.

The merge statement in the data set

UrvishShah — Tue, 20 Sep 2011 21:22:18 GMT

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;

The merge statement in the data set

art297 — Tue, 20 Sep 2011 22:16:11 GMT

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 merge statement in the data set

FriedEgg — Tue, 20 Sep 2011 22:25:29 GMT

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.

The merge statement in the data set

rtritz — Tue, 20 Sep 2011 22:28:01 GMT

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