DATA Step, Macro, Functions and more

Equating variable values for all observations within a BY group

Reply
N/A
Posts: 0

Equating variable values for all observations within a BY group

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
Super Contributor
Super Contributor
Posts: 3,174

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

Posted in reply to deleted_user
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.
N/A
Posts: 0

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

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;
Respected Advisor
Posts: 4,173

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

Posted in reply to deleted_user
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?
N/A
Posts: 0

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

this works - thanks so much for your help!

NG
PROC Star
Posts: 1,759

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

Posted in reply to deleted_user
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;
Ask a Question
Discussion stats
  • 5 replies
  • 199 views
  • 0 likes
  • 4 in conversation