BookmarkSubscribeRSS Feed
JosephCE
Calcite | Level 5

Dear all,

 

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

data have;

IDDATETIME
5000101/02/2018112132
5000112/12/2018141625
5000122/09/2018204958
5000102/05/2018115545
5000214/08/2018110038
5000227/11/2018125737
5000223/06/2018105847
5000222/12/2018111211
5000312/08/2018120045
5000314/08/2018105922
5000311/09/2018103040
5000313/08/2018141713


run;;

 

Therefore the output shall be:

 

IDDATETIME
5000122/09/2018204958
5000112/12/2018141625
5000227/11/2018125737
5000222/12/2018111211
5000314/08/2018105922
5000311/09/2018103040

  

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.

3 REPLIES 3
PaigeMiller
Diamond | Level 26

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;

    

--
Paige Miller
Astounding
PROC Star

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;

Kurt_Bremser
Super User

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;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 5158 views
  • 0 likes
  • 4 in conversation