SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Finding the last observation

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Finding the last observation

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;

 

IDWeight
12100
12102
12103
12105
12106
25210
25211
25212
25215
25216

Accepted Solutions
Solution
‎10-11-2016 09:32 AM
Super User
Posts: 5,516

Re: Finding the last observation

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

 

View solution in original post


All Replies
Super User
Posts: 19,858

Re: Finding the last observation

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

Occasional Contributor
Posts: 13

Re: Finding the last observation

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

Super User
Posts: 19,858

Re: Finding the last observation

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. 

Solution
‎10-11-2016 09:32 AM
Super User
Posts: 5,516

Re: Finding the last observation

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

 

Super User
Posts: 19,858

Re: Finding the last observation

Posted in reply to Astounding

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;

Super User
Posts: 5,516

Re: Finding the last observation

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.

Occasional Contributor
Posts: 13

Re: Finding the last observation

Posted in reply to Astounding
Thank you! I really appreciate it!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 616 views
  • 1 like
  • 3 in conversation