Get last datetime

[kz134]

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

[ballardw]

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

[PaigeMiller]

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

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

[Kurt_Bremser]

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

[hashman]

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