BookmarkSubscribeRSS Feed
Ruth
Fluorite | Level 6

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    .

6 REPLIES 6
Ruth
Fluorite | Level 6

Smiley Happy thanks for your instruction.

UrvishShah
Fluorite | Level 6

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
Opal | Level 21

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
SAS Employee

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
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 849 views
  • 3 likes
  • 5 in conversation