topic Re: Equating variable values for all observations within a BY group in SAS Programming

Equating variable values for all observations within a BY group

deleted_user — Sun, 20 Sep 2009 04:13:10 GMT

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

Re: Equating variable values for all observations within a BY group

sbb — Sun, 20 Sep 2009 07:47:58 GMT

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.

Re: Equating variable values for all observations within a BY group

deleted_user — Mon, 21 Sep 2009 02:39:32 GMT

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;

Re: Equating variable values for all observations within a BY group

Patrick — Mon, 21 Sep 2009 08:57:55 GMT

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?

Re: Equating variable values for all observations within a BY group

deleted_user — Mon, 21 Sep 2009 13:38:53 GMT

this works - thanks so much for your help!

NG

Re: Equating variable values for all observations within a BY group

ChrisNZ — Mon, 21 Sep 2009 22:44:40 GMT

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;