Question: are you asking for 7-day rolling sums? I notice that you have some missing dates in the time frame, but that can still be accommodated in a data step.
I'm not going to transcribe test data you provide in a picture instead of as text, so this program is untested. It assumes your data set (named HAVE below) is sorted by date, starts at Jan 1, 2019, and can have missing SEND_DATES:
data dummy_dates;
total_opens=0;
do send_date='01jan2019'd to today();
run;
data want;
merge dummy_dates have;
by send_date;
array opens {0:6} _temporary_ (7*0);
retain opens_rolling_n 0;
i=mod(date,7);
opens_rolling_n=opens_rolling_n-opens{i}+total_opens;
opens{i}=coalesce(total_opens,0);
run;
The technique here is to make a dummy dataset of all possible dates in your time frame, and assign TOTAL_OPEN=0 for each of those dates. Then merge this dataset (DUMMY_DATES) with HAVE by SENT_DATE. Any time a send_date is missing in HAVE, it is added with total_opens=0. When a send_date is present in both datasets, the value in HAVE prevails (because it is the rightmost dataset in the MERGE statement).
The program has an array for the 7 most recent values of total_open. A running OPENS_ROLLING_N is updated by adding in the current and subtracting out the 7-day old value (found in the array).
If you want to avoid having less than 7 day total in the first 6 observations, then:
data want (drop=_:);
merge dummy_dates have;
by send_date;
array opens {0:6} _temporary_ (7*0);
retain _opens 0;
i=mod(date,7);
_opens=_opens-opens{i}+total_opens;
opens{i}=coalesce(total_opens,0);
if _n_>6 then opens_rolling_n=_opens;
run;
... View more
