topic Re: Finding the last observation in SAS Data Management

Finding the last observation

llt34c — Mon, 10 Oct 2016 16:57:43 GMT

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

Re: Finding the last observation

Reeza — Mon, 10 Oct 2016 17:09:41 GMT

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

Re: Finding the last observation

llt34c — Mon, 10 Oct 2016 17:12:29 GMT

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

Re: Finding the last observation

Reeza — Mon, 10 Oct 2016 17:21:55 GMT

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.

Re: Finding the last observation

Astounding — Mon, 10 Oct 2016 17:35:28 GMT

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

Re: Finding the last observation

Reeza — Mon, 10 Oct 2016 22:31:17 GMT

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;

Re: Finding the last observation

Astounding — Mon, 10 Oct 2016 23:34:00 GMT

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.

Re: Finding the last observation

llt34c — Tue, 11 Oct 2016 13:32:29 GMT

Thank you! I really appreciate it!