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 |
I believe that you are missing a detail, such as "per ID per day". Is that the intent?
data have2;
set have;
date=datepart(datetime);
run;
proc summary nway data=have2;
class id date;
var datetime;
output out=want max=;
run;
proc sql;
create table want as
select
id,
datepart(datetime) format=yymmddd10. as date,
max(datetime)
from have
group by id, calculated date;
quit;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.