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 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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
  • 1172 views
  • 0 likes
  • 3 in conversation