Equating variable values for all observations within a BY group

[deleted_user]

Hi,

I have a data set with multiple observations per subject, and for one particular variable there is a value for the last observation for each subject, but for other observations within the subject the value is missing - e.g.,

id1 obs1 .
id1 obs2 .
id1 obs3 .
id1 obs4 x

How can I set the value of the variable to "x" within each subject's entire BY group, so that each observation for the same subject has the value "x"?

Thanks so much for any insight - I haven't been able to figure out a way to program this using FIRST.variables or LAST.variables.

NG

[sbb]

For a DATA step approach, create a separate file having only your BY variable(s) and the variable "x". Sort both files on the BY list. Then MERGE back in the newly created file with your primary file, using the BY statement variable list.

You will find several SAS-hosted DOC and supplemental technical/conference reference documents - suggest using the Google advanced search argument below to find matches with explanations and examples for both DATA step and PROC SQL approaches:

data step merge site:sas.com

Scott Barry
SBBWorks, Inc.

[deleted_user]

Thanks so much - I've tried this approach and it almost works, but I'm not sure whether I'm not sorting properly or whether something else happens when I merge. I've been using the code below, and whenever there are >2 observations for the same subject ID, I end up with missing values in the middle observation(s) within the subject ID BY group for the variable "x" (e.g., the first and last observations will have the proper value for x, but everything in between is still missing). Do you happen to know why this might be occurring?
Thanks again for your help - code below.
NG

data x_merge;
set x (keep=id x);
if x = . then delete;
run;
proc sort data=x;
by id;
run;
proc sort data=x_merge;
by id;
run;
data x_update;
merge x x_merge;
by id;
run;

[Patrick]

The following should work:

/* assuming data x is sorted by id, x */
data x_merge;
set x(keep=id x);
by id x;
if last.id then output;
run;

data x_update;
merge x(drop=x) x_merge;
by id;
run;


In your example:
- is x numeric or character?
- could it be that not only the last obs per id has a value and you end up with a many to many merge?

[deleted_user]

this works - thanks so much for your help!

NG

[ChrisNZ]

You can even do Patrick's suggestion in one go:

data x_update;
merge x(drop=x) x(keep=id x where=(x ne .));
by id;
run;