- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 09-28-2018 02:43 PM
(1175 views)
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Do you have a license for SAS /ETS (time series)?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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)
--------------------------
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
--------------------------
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
--------------------------