Add random variable

[Emma8]

Hi. Can anyone help? I have attached the below data. I would like to create a NEW_ID  if start dates are within a one-hour window then they should have the same (see NEW_ID) and also when the datetime2 is within one hour from the next sequential datetime1 (see AAC59 and AAC60). 

Also, NEW_ID: instead of sequential numbers (as you have seen in the attached file), could assign a random number between 100-999?

Thank you!

data have;
infile cards dsd;
input ID $ Datetime1 :datetime20. Datetime2 :datetime20.;* NEW_ID;
format datetime1 datetime2 datetime20.;
cards;
AAC56,25AUG20:02:00:00,25AUG20:02:00:00,1
AAC56,27AUG20:21:00:00,27AUG20:21:15:00,2
AAC56,31AUG20:10:30:00,31AUG20:11:30:00,3
AAC56,02SEP20:20:00:00,02SEP20:23:00:00,4
AAC56,29SEP20:21:43:00,29SEP20:22:03:00,5
AAC56,29SEP20:21:43:00,29SEP20:23:40:00,5
AAC56,29SEP20:22:03:00,29SEP20:22:03:00,5
AAC56,29SEP20:23:03:00,29SEP20:23:05:00,5
AAC56,30SEP20:00:03:00,30SEP20:00:13:00,5
AAC56,30SEP20:01:03:00,30SEP20:01:03:00,5
AAC58,28SEP20:13:45:00,28SEP20:13:49:00,1
AAC58,29SEP20:15:00:00,29SEP20:15:20:00,2
AAC58,29SEP20:16:00:00,29SEP20:16:15:00,2
AAC58,29SEP20:16:00:00,29SEP20:16:10:00,2
AAC58,29SEP20:17:10:00,29SEP20:17:12:00,2
AAC59,29SEP20:10:00:00,29SEP20:18:15:00,1
AAC59,29SEP20:16:00:00,29SEP20:16:10:00,1
AAC59,29SEP20:17:10:00,29SEP20:17:12:00,1
AAC60,29SEP20:08:00:00,29SEP20:16:15:00,1
AAC60,29SEP20:17:20:00,29SEP20:16:10:00,1
;

[CurtisMackWSIPP · Accepted solution]

I wasn't sure if different IDs should automatically be assigned different NEW_IDs.  Your example NEW_IDs appear to work one way for some records and the other for others.  Both approaches are done in the following examples.

proc sort data=have;
  by ID Datetime1 Datetime2;
run;

data want;
  set have;
  by ID Datetime1 Datetime2;
  retain NEW_ID2 0;
  sep1 = intck('hour',lag(Datetime1),Datetime1);
  sep2 = intck('hour',lag(Datetime2),Datetime2);
  if first.ID then NEW_ID2 = rand('integer', 100, 999);
  else if sep1 > 1 and sep2 > 1 then NEW_ID2 = rand('integer', 100, 999);
run;


proc sort data=have;
  by Datetime1 Datetime2;
run;

data want2;
  set have;
  by Datetime1 Datetime2;
  retain NEW_ID2 0;
  sep1 = intck('hour',lag(Datetime1),Datetime1);
  sep2 = intck('hour',lag(Datetime2),Datetime2);
  if _n_ = 1 then NEW_ID2 = rand('integer', 100, 999);
  else if sep1 > 1 and sep2 > 1 then NEW_ID2 = rand('integer', 100, 999);
run;

[CurtisMackWSIPP]

BTW, I forgot to mention that these random values will repeat randomly.  To prevent that is some significant additional code.

[Emma8]

Instead of random NEW_ID-s, could you create as in the have data, please? Thank you!

[CurtisMackWSIPP]

Its a pretty simple change...

data want;
  set have;
  by ID Datetime1 Datetime2;
  retain NEW_ID2 0;
  sep1 = intck('hour',lag(Datetime1),Datetime1);
  sep2 = intck('hour',lag(Datetime2),Datetime2);
  if first.ID then NEW_ID2 = 1;
  else if sep1 > 1 and sep2 > 1 then NEW_ID2 = NEW_ID2 + 1;
run;