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;

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