Please note the post has been updated:
How would I select the next to last row in SAS for each group of Column ID? Either with data step or proc sql.
Example: So I would for each ID want the next to last observation, meaning I want observation/row 3, 5, 13.
ID | Observation |
1 | 1 |
1 | 2 |
1 | 3 |
1 | 4 |
2 | 5 |
2 | 6 |
3 | 7 |
3 | 8 |
3 | 9 |
3 | 10 |
3 | 11 |
3 | 12 |
3 | 13 |
3 | 14 |
Delete the last row, then keep the new last row (which originally was the next to last row);
data want1;
set have;
by id;
if not last.id;
run;
data want2;
set want1;
by id;
if last.id;
run;
The same code I gave should work. Have you tried it?
When I see a question like this I just have to ask "why do you not want the last row for each group"?
Along with details such as "What if the group only has one observation?" There is no "next to last" in that case.
Would having all the values of other variable(s) with the same value modify the rule(s)? Or other variable(s) missing values modify the rule(s)?
Hi @SasStatistics,
You can also use the POINT= option of the SET statement to take one step back in the sequence of observations. The CUROBS= option helps to determine the appropriate observation number.
data want;
set have curobs=_n;
by id;
if last.id & ~first.id then do;
_p=_n-1;
set have point=_p;
output;
end;
run;
The condition ~first.id excludes IDs with only one observation from dataset WANT.
Nice work @FreelanceReinh and this generalizes to the case where you want the third to last row or the fourth to last row ...
@PaigeMiller wrote:
Nice work @FreelanceReinh and this generalizes to the case where you want the third to last row or the fourth to last row ...
Thanks. That generalization would likely involve the LAGn function (to compare IDs) or a counter variable instead of the ~first.id condition.
data have;
infile cards expandtabs;
input ID Observation;
cards;
1 1
1 2
1 3
1 4
2 5
2 6
3 7
3 8
3 9
3 10
3 11
3 12
3 13
3 14
;
data want;
do i=1 by 1 until(last.id);
set have;
by id;
end;
do j=1 by 1 until(last.id);
set have;
by id;
if j=i-1 then output;
end;
drop i j;
run;
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 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.