Selecting the next to last row for each ID group?

[SasStatistics]

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

[PaigeMiller]

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;

[SasStatistics]

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

[PaigeMiller]

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

[ballardw]

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

[FreelanceReinh]

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.

[PaigeMiller]

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

[FreelanceReinh]

---

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

[Ksharp]

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;