I am wanting to have SAS return the last weight for each ID. I was able to do this successfully with the intial weight but the final weight is pulling the last weight from the previous ID. Why is this happening?
Thanks!
data Test1; set Test;
by ID;
retain last_avrgweight;
if last.ID then last_avrgweight=AvrgWeight;
run;
ID | Weight |
12 | 100 |
12 | 102 |
12 | 103 |
12 | 105 |
12 | 106 |
25 | 210 |
25 | 211 |
25 | 212 |
25 | 215 |
25 | 216 |
Essentially, you are asking SAS to take the last value found, and then go back and add that to earlier observations. That can be done, but requires a more complex DATA step:
data want;
do until (last.id);
set have;
by id;
run;
last_avrgweight=AvrgWeight;
do until (last.id);
set have;
by id;
output;
end;
run;
The top DO loop reads all the observations for an ID, so the last value is available once that loop ends. The bottom loop reads the same observations, and outputs them (including the value for the new variable).
Your question is unclear. How is it not working? What are you trying to get vs what do you have?
I want SAS to take the last weight for each ID and list that as a new variable. Currently, SAS is taking the last weight from the previous ID and listing it as the new variable (thus this is the incorrect ID).
You have specified retain and only store it at the last record so that's correct behaviour.
It would help if you illustrated what you wanted but right now my suggestion would be to remove retain.
Essentially, you are asking SAS to take the last value found, and then go back and add that to earlier observations. That can be done, but requires a more complex DATA step:
data want;
do until (last.id);
set have;
by id;
run;
last_avrgweight=AvrgWeight;
do until (last.id);
set have;
by id;
output;
end;
run;
The top DO loop reads all the observations for an ID, so the last value is available once that loop ends. The bottom loop reads the same observations, and outputs them (including the value for the new variable).
Or sort the other direction...
proc sort data=have;
by id descending amount;
data want;
set have;
by id;
retain lastVal;
if first.id then lastVal=amount;
run;
proc sort data=want;
by id amount;
run;
That works as long as the last value is also the largest value. That might be true here, and the sample data shows it that way. But I didn't want to assume it if it wasn't stated.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.