Creating a 7 week average variable

[scolitti1]

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

[Reeza]

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

---

[ballardw]

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.

[scolitti1]

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

[ballardw]

---

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

[Reeza]

---

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