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 | ... | ... | ... |
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 ... ... ...
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 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.
@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.
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.