BookmarkSubscribeRSS Feed
mac91red
Calcite | Level 5

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

3 REPLIES 3
Reeza
Super User
Do you have a license for SAS /ETS (time series)?
PGStats
Opal | Level 21

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;
PG
mkeintz
PROC Star

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)
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1315 views
  • 0 likes
  • 4 in conversation