topic Re: Get last datetime in SAS Programming

Get last datetime

kz134 — Tue, 24 Sep 2019 14:49:58 GMT

Hello,

Could someone please help me with getting the most recent date of a dataset, but with date time?

I have:

ID	Datetime
ABC	13SEP19:18:46:44
ABC	13SEP19:18:46:47
ABC	13SEP19:18:46:53
ABC	20SEP19:17:51:04
ABC	20SEP19:17:51:05
ABC	20SEP19:17:51:08
ABC	20SEP19:17:51:15
ABC	20SEP19:17:51:17
ABC	20SEP19:17:51:19
ABC	20SEP19:17:51:20
ABC	23SEP19:21:03:23
ABC	23SEP19:21:03:26
ABC	23SEP19:21:03:28
ABC	23SEP19:21:03:35
ABC	23SEP19:21:03:38
ABC	23SEP19:21:03:41
ABC	23SEP19:21:03:46
ABC	23SEP19:21:03:49

I want:

ID	Datetime
ABC	13SEP19:18:46:53
ABC	20SEP19:17:51:20
ABC	23SEP19:21:03:49

Re: Get last datetime

ballardw — Tue, 24 Sep 2019 15:00:56 GMT

I believe that you are missing a detail, such as "per ID per day". Is that the intent?

Re: Get last datetime

PaigeMiller — Tue, 24 Sep 2019 15:01:09 GMT

data have2;
    set have;
    date=datepart(datetime);
run;

proc summary nway data=have2;
     class id date;
     var datetime;
     output out=want max=;
run;

Re: Get last datetime

Kurt_Bremser — Tue, 24 Sep 2019 17:08:20 GMT

proc sql;
create table want as
select
  id,
  datepart(datetime) format=yymmddd10. as date,
  max(datetime)
from have
group by id, calculated date;
quit;

Re: Get last datetime

hashman — Wed, 25 Sep 2019 01:34:50 GMT

@kz134:

If your input file is sorted by [ID, datetime], the need to create an extra date part variable in a separate step (creating a data set or view) can be circumvented by using the GROUPFORMAT option on the BY statement:

data have ;                                  
  input id $ datetime :anydtdtm21. extravar ;
  cards ;                                    
ABC  13SEP19:18:46:44   1                    
ABC  13SEP19:18:46:47   2                    
ABC  13SEP19:18:46:53   3                    
ABC  20SEP19:17:51:04   4                    
ABC  20SEP19:17:51:05   5                    
ABC  20SEP19:17:51:08   6                    
ABC  20SEP19:17:51:15   7                    
ABC  20SEP19:17:51:17   8                    
ABC  20SEP19:17:51:19   9                    
ABC  20SEP19:17:51:20  10                    
ABC  23SEP19:21:03:23  11                    
ABC  23SEP19:21:03:26  12                    
ABC  23SEP19:21:03:28  13                    
ABC  23SEP19:21:03:35  14                    
ABC  23SEP19:21:03:38  15                    
ABC  23SEP19:21:03:41  16                    
ABC  23SEP19:21:03:46  17                    
ABC  23SEP19:21:03:49  18                    
;                                            
run ;                                        
                                             
data want ;                                  
  set have ;                                 
  by id datetime groupformat ;               
  format datetime E8601DN. ;                 
  if last.datetime ;                         
run ;

Note that if you open WANT in the viewer, you'll see the DATETIME variable displayed as YYYY-MM-DD due to the ISO format E8601DNw. attached to it. However, its underlying values are still the original datetime values. To change the format back to DATETIMEw., you can follow the above step by:

proc sql ;                                           
  alter table want modify datetime format=datetime. ;
quit ;

(or, alternatively, use the DATASETS procedure instead). Though it's an extra step, it won't affect performance since it doesn't read the data from WANT but only modifies the format in its descriptor.

Kind regards

Paul D.