Try this:
data have ;
/* create newId and retain it */
retain newid 1 ;
format datetime datetime. ;
infile want delimiter="," firstobs=2 ;
input
ID $
datetime datetime16.
;
/* Lag Function returns the prior value of datetime */
lagdt=lag1(datetime) ;
/* if current datetime-lagdt>60*60 (1 hour) then increment new id */
if datetime-lagdt>60*60 then do ;
newId+1 ;
end ;
run ;
data have;
infile cards dsd;
input ID $ Datetime :datetime20.;* NEW_ID;
format datetime datetime20.;
cards;
AAC56,25AUG20:02:00:00,1
AAC56,27AUG20:21:00:00,2
AAC56,31AUG20:10:30:00,3
AAC56,02SEP20:20:00:00,4
AAC56,29SEP20:21:43:00,5
AAC56,29SEP20:21:43:00,5
AAC56,29SEP20:22:03:00,5
;
data want;
do until(last.id);
set have;
by id;
if first.id or intck('min',_n_,datetime)>60 then do;
new_id=sum(new_id,1);
_n_=datetime;
end;
output;
end;
run;
ID | Datetime | new_id |
---|---|---|
AAC56 | 25AUG2020:02:00:00 | 1 |
AAC56 | 27AUG2020:21:00:00 | 2 |
AAC56 | 31AUG2020:10:30:00 | 3 |
AAC56 | 02SEP2020:20:00:00 | 4 |
AAC56 | 29SEP2020:21:43:00 | 5 |
AAC56 | 29SEP2020:21:43:00 | 5 |
AAC56 | 29SEP2020:22:03:00 | 5 |
Hi @Emma8 My apologies for overlooking the question. Alright, I believe I understood what you mean. Can you please clarify this one -
AAC56,25AUG20:02:00:00,1
AAC56,27AUG20:21:00:00,1
as the time difference seem way off besides it's 2 days apart. So wouldn't it be 1,2(new_id)?
Hi again. Can you please review this one too-
AAC58,29SEP20:16:00:00,2
AAC58,29SEP20:17:10:00,2
Rather, Can you please revise the expected output for my correct reference please.
Hi @Emma8 Thank you. Please try-
data have;
infile cards dsd;
input ID $ Datetime :datetime20.;* NEW_ID;
format datetime datetime20.;
cards;
AAC56,25AUG20:02:00:00,1
AAC56,27AUG20:21:00:00,2
AAC56,31AUG20:10:30:00,3
AAC56,02SEP20:20:00:00,4
AAC56,29SEP20:21:43:00,5
AAC56,29SEP20:21:43:00,5
AAC56,29SEP20:22:03:00,5
AAC56,29SEP20:23:03:00,5
AAC56,30SEP20:00:03:00,5
AAC56,30SEP20:01:03:00,5
AAC58,28SEP20:13:45:00,1
AAC58,29SEP20:15:00:00,2
AAC58,29SEP20:16:00:00,2
AAC58,29SEP20:16:00:00,2
AAC58,29SEP20:17:10:00,2
;
data want;
do until(last.id);
set have;
by id;
if first.id or intck('hour',_n_,datetime)>1 then new_id=sum(new_id,1);
_n_=datetime;
output;
end;
run;
Fyi- The correction is-
if first.id or intck('hour',_n_,datetime)>1 then new_id=sum(new_id,1);
_n_=datetime;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.