Calculate 7 day average by date

[Abz_17]

Hi All, 

I have a dataset which starts on 4th Jan 2021. What I want to do is show the rolling 7 day average for each of these dates so for example 12/02/2023 i want to know what the average has been over the last 7 days, and on 11/02/2023 the average over the last 7 days of that date etc etc. I have attached my sample file, if anyone could help that would be great. 

[PaigeMiller]

If you have SAS/ETS licensed, you can use PROC EXPAND. See the examples here: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.4/etsug/etsug_expand_details19.htm#etsug_expand...

[ballardw]

---

@Abz_17 wrote:

Hi All, 

I have a dataset which starts on 4th Jan 2021. What I want to do is show the rolling 7 day average for each of these dates so for example 12/02/2023 i want to know what the average has been over the last 7 days, and on 11/02/2023 the average over the last 7 days of that date etc etc. I have attached my sample file, if anyone could help that would be great. 

---

Average of what?

If it is of the column in that text file called Rejection_rate, which shows a percent sign in the value, then you need to be very aware that averages of percent values are seldom the actual percent average unless the denominator is identical for every record:

Example

n   reject   rejection rate

2   1          50%

1000   1    0.1%

if you "average" those two you get a result of 50.1 / 2 = 25.05%. But the combined rate is (1 +1)/(2+1000) which is 0.2% (rounded somewhat from 0.199601

If your denominator is similar every single observation then just have smaller less obvious errors.

So if you want to do a running rejection rate you have to accumulate the numerators and denominators for each observation before calculating a "running rate".

[Kurt_Bremser]

To keep a rolling window, use an array:

data want;
set have;
array val {0:6} _temporary_;
x = mean(of val{*});
val{mod(_n_,7)} = rate;
run;