topic Calculate 7 day average by date in New SAS User

Calculate 7 day average by date

Abz_17 — Tue, 14 Feb 2023 14:44:23 GMT

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.

Re: Calculate 7 day average by date

PaigeMiller — Tue, 14 Feb 2023 15:14:38 GMT

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_expand001988

Re: Calculate 7 day average by date

ballardw — Tue, 14 Feb 2023 15:32:18 GMT

@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".

Re: Calculate 7 day average by date

Kurt_Bremser — Tue, 14 Feb 2023 21:16:29 GMT

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;