topic Re: Selecting the next to last row? in SAS Programming

Selecting the next to last row for each ID group?

SasStatistics — Tue, 14 Jun 2022 13:47:03 GMT

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

Re: Selecting the next to last row?

PaigeMiller — Tue, 14 Jun 2022 13:40:34 GMT

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;

Re: Selecting the next to last row?

SasStatistics — Tue, 14 Jun 2022 13:47:24 GMT

Had to update the post, sorry. Do you know the solution in this updated case?

Re: Selecting the next to last row?

PaigeMiller — Tue, 14 Jun 2022 13:56:28 GMT

The same code I gave should work. Have you tried it?

Re: Selecting the next to last row for each ID group?

ballardw — Tue, 14 Jun 2022 14:16:55 GMT

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

Re: Selecting the next to last row for each ID group?

FreelanceReinh — Tue, 14 Jun 2022 18:09:14 GMT

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.

Re: Selecting the next to last row for each ID group?

PaigeMiller — Tue, 14 Jun 2022 18:21:05 GMT

Nice work @FreelanceReinh and this generalizes to the case where you want the third to last row or the fourth to last row ...

Re: Selecting the next to last row for each ID group?

FreelanceReinh — Tue, 14 Jun 2022 18:32:00 GMT

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

Re: Selecting the next to last row for each ID group?

Ksharp — Wed, 15 Jun 2022 12:20:37 GMT

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;