CODEWITHSUNDEEP
sassas-macro
Mark
Mark

Reputation: 347

Count instances within a certain timeframe

Good afternoon,

I have a set of data that has an ID and a create timestamp. If the timestamps are within 15 minutes of each other for each ID, it is considered 1 "occurrence". There can be more than 1 occurrence per ID. As soon as the timestamp is > 15 min from the first for the id, I need it to be considered as a new occurrence. Then it starts over. From the new occurrence, look at the next record and if it is 15 minutes from the timestamp consider it a single occurrence, so on and so forth.

I hope that makes sense.

example below.

ID              TIMESTAMP           OCCURRENCE
123abc  7/19/2022 16:32                  1
123abc  7/19/2022 16:35 
123abc  7/19/2022 16:37 
123abc  7/19/2022 16:39 
123abc  7/19/2022 17:32                  1
123abc  7/19/2022 17:40 
123abc  7/19/2022 17:42 
123abc  7/20/2022 19:35                  1
123abc  7/21/2022 16:35                  1
123abc  7/22/2022 23:42                  1
123abc  7/22/2022 23:44 
123abc  7/22/2022 23:45 
123abc  7/22/2022 23:58                  1
456deg  7/19/2022 16:42                  1
456deg  7/19/2022 16:44 
456deg  7/19/2022 17:15                  1
456deg  7/19/2022 17:18

I'm not sure where to start. I'm intermediate in sas but not a code macro'er. Could someone help me out or point me into the right direction?

Upvotes: 0

Views: 109

Answers (1)

Tom
Tom

Reputation: 51621

If the data is sorted by ID and TIMESTAMP then you can quickly number the occurrences by just using the DIF() function to calculate the difference in seconds between the two datetime values.

data want;
  set have;
  by id timestamp;
  gap = dif(timestamp);
  format gap time12.;
  if first.id then occurrence=1;
  else if gap > '00:15:00't then occurence+1;
run;

Post actual data as text (not photographs) to get tested code.

Upvotes: 1

Related Questions