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:
_n1, to point at the current time element
_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)
... View more
