topic Test in SAS Programming

Test

Emma8 — Wed, 14 Oct 2020 22:06:55 GMT

Re: Add New ID-s: if within one hour then the same IDs

AMSAS — Wed, 14 Oct 2020 18:58:48 GMT

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 ;

Re: Add New ID-s: if within one hour then the same IDs

novinosrin — Wed, 14 Oct 2020 18:59:36 GMT


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

Test

Emma8 — Fri, 16 Oct 2020 00:05:12 GMT

Re: Add New ID-s: if within one hour then the same IDs

novinosrin — Thu, 15 Oct 2020 03:09:13 GMT

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

Re: Add New ID-s: if within one hour then the same IDs

Emma8 — Thu, 15 Oct 2020 03:10:29 GMT

Sorry that should be 1 and 2

Re: Add New ID-s: if within one hour then the same IDs

novinosrin — Thu, 15 Oct 2020 03:17:44 GMT

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.

Test

Emma8 — Fri, 16 Oct 2020 00:10:06 GMT

Re: Add New ID-s: if within one hour then the same IDs

novinosrin — Thu, 15 Oct 2020 03:39:43 GMT

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;