The merge statement in the data set

[Ruth]

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    .

[FriedEgg]

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

[Ruth]

thanks for your instruction.

[UrvishShah]

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;

[art297]

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;

[FriedEgg]

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.

[rtritz]

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