topic how to get the last observation of a group of data in SAS Enterprise Guide

how to get the last observation of a group of data

JosephCE — Fri, 31 Aug 2018 18:56:33 GMT

Dear all,

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

data have;

ID	DATE	TIME
500001	10/08/2018	141110
500001	21/08/2018	104404
500001	22/08/2018	140100
500002	12/06/2018	103810
500002	10/08/2018	153104
500002	14/08/2018	125737
500003	04/08/2018	094524
500003	06/08/2018	134535
500003	08/08/2018	115742
500003	10/08/2018	164127
500003	12/08/2018	092203
500003	14/08/2018	105922

;

run;

Therefore the output shall be:

ID	DATE	TIME
500001	22/08/2018	140100
500002	14/08/2018	125737
500003	14/08/2018	105922

Thank you all.

Re: how to get the last observation of a group of data

novinosrin — Fri, 31 Aug 2018 19:14:24 GMT

data have;
input ID $	DATE  :ddmmyy10.	TIME;
format date ddmmyy10.  ;
cards;
500001	10/08/2018	141110
500001	21/08/2018	104404
500001	22/08/2018	140100
500002	12/06/2018	103810
500002	10/08/2018	153104
500002	14/08/2018	125737
500003	04/08/2018	094524
500003	06/08/2018	134535
500003	08/08/2018	115742
500003	10/08/2018	164127
500003	12/08/2018	092203
500003	14/08/2018	105922
;

proc sql;
create table want(drop=dt) as
select *,dhms(date,0,0,time) as dt
from have
group by id
having dt=max(dt);
quit;

Re: how to get the last observation of a group of data

ballardw — Fri, 31 Aug 2018 19:20:01 GMT

If the data is sorted so that the last is correct then

data want;
   set have;
   by id;
   if last.id;
run;

Re: how to get the last observation of a group of data

novinosrin — Fri, 31 Aug 2018 19:22:17 GMT

data have;
input ID $	DATE  :ddmmyy10.	TIME;
format date ddmmyy10.  ;
cards;
500001	10/08/2018	141110
500001	21/08/2018	104404
500001	22/08/2018	140100
500002	12/06/2018	103810
500002	10/08/2018	153104
500002	14/08/2018	125737
500003	04/08/2018	094524
500003	06/08/2018	134535
500003	08/08/2018	115742
500003	10/08/2018	164127
500003	12/08/2018	092203
500003	14/08/2018	105922
;

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;