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-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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