Help using Base SAS procedures

The merge statement in the data set

Reply
Contributor
Posts: 52

The merge statement in the data set

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    .

Trusted Advisor
Posts: 1,301

The merge statement in the data set

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

Contributor
Posts: 52

The merge statement in the data set

Smiley Happy thanks for your instruction.

Regular Contributor
Posts: 195

The merge statement in the data set

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;

PROC Star
Posts: 7,471

The merge statement in the data set

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;

Trusted Advisor
Posts: 1,301

The merge statement in the data set

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.

Contributor
Posts: 23

The merge statement in the data set

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

Ask a Question
Discussion stats
  • 6 replies
  • 141 views
  • 3 likes
  • 5 in conversation