topic Creating a 7 week average variable in New SAS User

Creating a 7 week average variable

scolitti1 — Fri, 26 Aug 2022 16:53:00 GMT

Hi,

I have a spreadsheet that tracks daily rates for a specific outcome, and I report using the 7-day average of the whole week. I've been going in the excel spreadsheet manually to calculate the average of each week, but as the spreadsheet is getting bigger, it's been getting to be too much. I would like to have an open-ended end date because the excel spreadsheet grows daily.

What spreadsheet looks like:

AdmitDate	Outcome 1	Outcome 2	Outcome 3
05/02/2021	.4013	.47752	.6059
05/03/2021	.4013	.35814	.7596
05/04/2021	.6019	.35814	.7632
05/05/2021	.6019	.23876	.7669
05/06/2021	1.0031	.35814	.7708
05/07/2021	1.0031	.35814	.7752
05/08/2021	1.0031	.35814	.6244
05/09/2021	...	...	...

Here is how the end product should look like:

Week Ending	Outcome 1	Outcome 2	Outcome 3
5/8/2021	.7165	.35814	.7230
5/15/2021	...	...	...

Re: Creating a 7 week average variable

Reeza — Fri, 26 Aug 2022 17:02:54 GMT

proc means data=have mean;
class admitDate;
format admitDate weekv.;
var outcome:;
ods output summary = want;
run;

You can modify the format to ensure it aligns with your dates.

@scolitti1 wrote:

Hi,

I have a spreadsheet that tracks daily rates for a specific outcome, and I report using the 7-day average of the whole week. I've been going in the excel spreadsheet manually to calculate the average of each week, but as the spreadsheet is getting bigger, it's been getting to be too much. I would like to have an open-ended end date because the excel spreadsheet grows daily.

What spreadsheet looks like:

AdmitDate Outcome 1 Outcome 2 Outcome 3

05/02/2021 .4013 .47752 .6059

05/03/2021 .4013 .35814 .7596

05/04/2021 .6019 .35814 .7632

05/05/2021 .6019 .23876 .7669

05/06/2021 1.0031 .35814 .7708

05/07/2021 1.0031 .35814 .7752

05/08/2021 1.0031 .35814 .6244

05/09/2021 ... ... ...

Here is how the end product should look like:

Week Ending Outcome 1 Outcome 2 Outcome 3

5/8/2021 .7165 .35814 .7230

5/15/2021 ... ... ...

Re: Creating a 7 week average variable

ballardw — Fri, 26 Aug 2022 17:18:25 GMT

What type of "Rate" is involved? If it is one that is calculated with a changing denominator (people shopping in a store per day as one example) then averaging rates is inappropriate. Since you have an "admit_date" one might assume the rate involves the number of "admissions" of some sort. If the number of admissions on each day is not the same then this average is suspect.

Example:

data example;
   input admitdate :date9. admits outcome1;
   outcome1rate = outcome1/admits;
datalines;
01APR2022   500   100
02Apr2022   5     4
;

Which would have daily rates of 0.2 and 0.8. Averaging those would give you a 0.5 but the actual combined rate for two days should be 104/505 or about 0.2059

If your denominator is pretty consistent from day to day you may not have problems but if not you really should total the counts for the period get the combined rate for a period.

Re: Creating a 7 week average variable

scolitti1 — Fri, 26 Aug 2022 17:42:40 GMT

This is a crude rate. Denominator is the same from day to day

Re: Creating a 7 week average variable

ballardw — Fri, 26 Aug 2022 19:28:58 GMT

@scolitti1 wrote:
This is a crude rate. Denominator is the same from day to day

Thank you for answering and I am glad for you. I am a touch sensitive to this subject as almost none of my data has similar, much less identical, denominators and I catch folks trying to average rates from the data entirely too often.

Re: Creating a 7 week average variable

Reeza — Fri, 26 Aug 2022 20:32:33 GMT

@scolitti1 wrote:
This is a crude rate. Denominator is the same from day to day

Then the format approach suggested should work.

If you want the data shown as a day of the week as in your post, remove the format on the output data set.