Weekly averages of daily data

[starz4ever2007]

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?

[RahulG]

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;

[starz4ever2007]

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

[RahulG · Accepted solution]

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.

[starz4ever2007]

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

[RahulG]

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 

[starz4ever2007]

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

[RahulG]

You should have marked solution to my reply instead on to your own post

[starz4ever2007]

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