topic Re: Merging in SAS Programming

Merging

dhrums28 — Tue, 07 Nov 2017 19:40:05 GMT

Hi,

Data A:

sub model

1 CT

1 US

1 xR

1 CP

data B:

sub model_site

1 CT

1 FG

1 YG

need final result:

sub model model_site

1 CT CT

1 US FG

1 xR YG

1 CP

basically i have 2 different data set with subject and model. 'A' data set has 4 observations and 'B' data set has 3 observation. i need to merge this data set by Subject but i need to last row of model_site is missing.

Re: Merging

Astounding — Tue, 07 Nov 2017 20:04:00 GMT

Assuming you have already sorted your data sets, here is the simplest way:

data want;

merge A B;

by sub;

output;

model = ' ';

model_site = ' ';

run;

Re: Merging

Reeza — Tue, 07 Nov 2017 20:29:59 GMT

Do you have multiple subjects?

Otherwise a MERGE without a BY statement is all you need, but I highly suspect your problem is more complicated.

Make sure your sample data accurately reflects your real data.

Re: Merging

Tom — Tue, 07 Nov 2017 20:46:31 GMT

Normally that is not what people want to do when they have two datasets with repeating by groups, but luckily for you it is very similar to how SAS data step merge handles combining observations when datasets contribute multiple observations per group. But normal SAS behavior is to retain the values from the last observation of the dataset with fewer observations.

You cannot prevent the retaining, but you can add code to write the observation and then clear all of the variables so nothing is retained.

data want ;
  merge A B ;
  by sub ;
  output;
  call missing(of _all_);
run;