BookmarkSubscribeRSS Feed
Emma8
Quartz | Level 8
 
8 REPLIES 8
AMSAS
SAS Super FREQ

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 ;
novinosrin
Tourmaline | Level 20

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
Emma8
Quartz | Level 8
 
novinosrin
Tourmaline | Level 20

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)?

Emma8
Quartz | Level 8
Sorry that should be 1 and 2
novinosrin
Tourmaline | Level 20

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. 

Emma8
Quartz | Level 8
 
novinosrin
Tourmaline | Level 20

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-2026-white.png



April 27 – 30 | Gaylord Texan | Grapevine, Texas

Registration is open

Walk in ready to learn. Walk out ready to deliver. This is the data and AI conference you can't afford to miss.
Register now and lock in 2025 pricing—just $495!

Register now

How to Concatenate Values

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.

SAS Training: Just a Click Away

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

Browse our catalog!

Discussion stats
  • 8 replies
  • 2061 views
  • 0 likes
  • 3 in conversation