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
;
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;
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;
BTW, I forgot to mention that these random values will repeat randomly. To prevent that is some significant additional code.
Instead of random NEW_ID-s, could you create as in the have data, please? Thank you!
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.