BookmarkSubscribeRSS Feed
scolitti1
Calcite | Level 5

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:

AdmitDateOutcome 1Outcome 2Outcome 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/20211.0031.35814.7708
05/07/20211.0031.35814.7752
05/08/20211.0031.35814.6244
05/09/2021.........

 

Here is how the end product should look like:

 

Week EndingOutcome 1Outcome 2Outcome 3
5/8/2021.7165.35814.7230
5/15/2021.........

 

5 REPLIES 5
Reeza
Super User
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
Super User

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
Calcite | Level 5
This is a crude rate. Denominator is the same from day to day
ballardw
Super User

@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
Super User

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

 

SAS Innovate 2025: Register Now

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!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 5 replies
  • 785 views
  • 1 like
  • 3 in conversation