Solved
Contributor
Posts: 45

# Weekly averages of daily data

[ Edited ]

I have data that looks like this:

subjid      date          week    score

001          2/3/16         1         3

001          2/4/16         1         2

001          2/5/16         1         3

001          2/6/16         1         1

001          2/7/16         1         3

001          2/8/16         1         3

001          2/9/16         1         3

001          2/10/16       2         3

001          2/11/16       2         2

001          2/12/16       2         1

001          2/13/16       2         3

001          2/18/16       3         3

001          2/19/16       3         1

002          1/7/16         1         3

002          1/8/16         1         3

002          1/9/16         1         3

I need to calculate the weekly average of the scores (per subjid per week) so that I will have a weekly average for subj 001 week 1, subj 001 week 2, subj 001 week 3, subj 002 week 1...and so on. Thing to note: If there are more than four days in the week missing then the weekly average of the score should be missing. Anyone have any idea how to do this?

Accepted Solutions
Solution
‎07-26-2016 01:10 AM
Super Contributor
Posts: 271

## Re: Weekly averages of daily data

Try this out for your first query.  You may have to play around with class variable as per your required output.

proc mean data=input_dataset   n mean std;

class week;

var score;

run;

Frequency count of the score for every subjid

proc mean data=input_dataset   n;

class subjid score ;

var score;

run;

Second one can also be done with proc freq/tabulate.

All Replies
Super Contributor
Posts: 271

## Re: Weekly averages of daily data

[ Edited ]

I have applied a condition that more than 3 days data in a week should be available for every subjid

proc sql;
select subjid, week, avg(score)
from input
group by subjid, week
having count(*) > 3;
quit;

Contributor
Posts: 45

## Re: Weekly averages of daily data

[ Edited ]

thank you! that helped!...along with the mean, can i get the standard deviations?...and is there a way can i get the average for all patients in week 1 score 1, week 1 score 2, week 1 score 3, week 2 score 1, week 2 score 2,...

also, how can i do the frequency count of the scores for each week (example for id 001, how many people had score 1, score 2, score 3 for each week...same with id 002...etc...

Solution
‎07-26-2016 01:10 AM
Super Contributor
Posts: 271

## Re: Weekly averages of daily data

Try this out for your first query.  You may have to play around with class variable as per your required output.

proc mean data=input_dataset   n mean std;

class week;

var score;

run;

Frequency count of the score for every subjid

proc mean data=input_dataset   n;

class subjid score ;

var score;

run;

Second one can also be done with proc freq/tabulate.

Contributor
Posts: 45

## Re: Weekly averages of daily data

where would you incorporate "having count (*) >3". I would only want the means and counts for weeks that had more than three scores?

Super Contributor
Posts: 271

## Re: Weekly averages of daily data

Lets make it simple

proc sql;

create table temp_tab1 as
select subjid, week
from input
group by subjid, week
having count(*) > 3;

create table temp_tab2 as

select a.*

from input  a inner join temp_tab1 b

on a.subjid=b.subjid and a.week=b.week;

quit;

Now use proc means statitistics on temp_tab2

Contributor
Posts: 45

## Re: Weekly averages of daily data

thank you for all your help! i appreciate it it worked for me! and thank you for the quick replies!

Super Contributor
Posts: 271

Contributor
Posts: 45

## Re: Weekly averages of daily data

sorry about that...changed the solution back to you!

☑ This topic is solved.