counter that resets after 4 hours

[mac91red]

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

[Reeza]

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

[PGStats]

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;

[mkeintz]

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;

1. 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
2. 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)