topic Re: how to get the last two observations of a group of data in SAS Enterprise Guide

how to get the last two observations of a group of data

JosephCE — Mon, 03 Sep 2018 23:27:59 GMT

Dear all,

For the following sample dataset I need to retrieve the last value of DATE and TIME within each group (variableID).

data have;

ID	DATE	TIME
50001	01/02/2018	112132
50001	12/12/2018	141625
50001	22/09/2018	204958
50001	02/05/2018	115545
50002	14/08/2018	110038
50002	27/11/2018	125737
50002	23/06/2018	105847
50002	22/12/2018	111211
50003	12/08/2018	120045
50003	14/08/2018	105922
50003	11/09/2018	103040
50003	13/08/2018	141713

run;;

Therefore the output shall be:

ID	DATE	TIME
50001	22/09/2018	204958
50001	12/12/2018	141625
50002	27/11/2018	125737
50002	22/12/2018	111211
50003	14/08/2018	105922
50003	11/09/2018	103040

I have the following code but it only shows me the last one,

data want;
if 0 then set have;
call missing(dt);
do until(last.id);
set have;
by id;
dt=dt<>dhms(date,0,0,time);
end;
drop dt;
run;

Thank you all.

Re: how to get the last two observations of a group of data

PaigeMiller — Mon, 03 Sep 2018 23:41:09 GMT

UNTESTED CODE

data have2;
     set have;
     by id;
     if first.id then count=0;
     count+1;
run;
proc sql;
     create table want as select * from have2 
     where count>=max(count)-1
     group by id;
quit;

Re: how to get the last two observations of a group of data

Astounding — Tue, 04 Sep 2018 00:44:45 GMT

If you want the last observation, you will need to sort your data. Easiest for your purposes:

proc sort data=have;

by id descending date descending time;

run;

Then you can select the first two observations for each ID:

data want;

set have;

by id;

if first.id then counter=1;

else counter + 1;

if counter <= 2;

drop counter;

run;

Re: how to get the last two observations of a group of data

Kurt_Bremser — Tue, 04 Sep 2018 06:50:45 GMT

What did you intend dt to be? You create this variable (BTW, it will always end up zero), but do not do anything with it besides dropping it.

If you want the last two observations within a group, sort in reverse order, set a counter to 1 at first., and counter + 1 else, and use a subsetting if for counter <= 2.

proc sort data=have;
by id descending date descending time;
run;

data want;
set have;
by id;
if first.id
then counter = 1;
else counter + 1;
if counter <= 2;
drop counter;
run;

proc sort data=want;
by id date time;
run;