topic Re: counter that resets after 4 hours in SAS Procedures

counter that resets after 4 hours

mac91red — Fri, 28 Sep 2018 18:43:34 GMT

I am hoping to create a rolling counter but im struggling with a do loop. The counter needs to count the number or records in a rolling 4 hour period by account. I've included a sample of data and the expected counter output. Appreciate the help

Re: counter that resets after 4 hours

Reeza — Fri, 28 Sep 2018 18:51:10 GMT

Do you have a license for SAS /ETS (time series)?

Re: counter that resets after 4 hours

PGStats — Fri, 28 Sep 2018 20:48:06 GMT

Combine date and time variables into datetime variable called dt and run:

data want(keep=account dt counter);
array d {12}; /* Adjust limit to max number of obs per 4-hour window */
pos = 0;
do until(last.account);
    set have; by account;
    counter = 1;
    do i = 1 to dim(d);
        if dt - '04:00:00't >= d{i} then call missing(d{i});
        else counter = counter + 1;
        end;
    output;
    d{pos + 1} = dt;
    pos = mod(pos + 1, dim(d));
    end;
run;

Re: counter that resets after 4 hours

mkeintz — Sat, 29 Sep 2018 04:20:36 GMT

Like the @PGStats response, this uses an array to hold time values. However, the array dimension should be set to the largest expected number of records for a single date:

data have;
  input account date :$6. time :time5.0 expected;
  format time time5.0;
datalines;
1 1-Aug  1:00 1
1 1-Aug  1:30 2
1 1-Aug  2:00 3
1 1-Aug  3:30 4
1 1-Aug  4:30 5
1 1-Aug  5:00 5
1 1-Aug  6:30 4
1 1-Aug  7:30 5
2 1-Aug 13:00 1
2 1-Aug 14:00 2
2 2-Aug 15:00 1
2 2-Aug 16:00 2
run;

data want (drop=_:);
  array tims {20} _temporary_;
  set have;
  by account date;

  if first.date then call missing(_n1,counter,of tims{*});
  _n1+1;
  tims{_n1}=time;
  counter+1;

  if first.date then _n2=1;
  do counter=counter by -1 while (tims{_n2} <= time-'04:00:00't);
	tims{_n2}=.;
	_n2+1;
  end;
run;

The program maintains 2 variables for indexing the time arrays:
1. _n1, to point at the current time element
2. _n2, to point at the earliest element having time within the 4-hour window. So _N2 is always <= _N1
I believe your expected counter value for 7:30 1-Aug is in error. You have counter=5, but that would require including the 3:30 record, exactly 4 hours earlier. That would be inconsistent with your 5:00 record with counter=5, because it doesn't include the earlier 1:00 record. Now if you do want to include records exactly 4 hours old, then change the
while (tims{_n2} <= time-'04:00:00't)
to
while (tims{_n2} < time-'04:00:00't)