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;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.