Finding the last observation

[llt34c]

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

[Reeza]

Your question is unclear. How is it not working? What are you trying to get vs what do you have?

[llt34c]

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).

[Reeza]

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. 

[Astounding · Accepted solution]

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).

[Reeza]

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;

[Astounding]

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.

[llt34c]

Thank you! I really appreciate it!