Weekly averages of daily data

Accepted Solution Solved
Reply
Contributor
Posts: 45
Accepted Solution

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
Regular Contributor
Posts: 236

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.

View solution in original post


All Replies
Regular Contributor
Posts: 236

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
Regular Contributor
Posts: 236

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?

Regular Contributor
Posts: 236

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 Smiley Happy it worked for me! and thank you for the quick replies!

Regular Contributor
Posts: 236

Re: Weekly averages of daily data

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

Contributor
Posts: 45

Re: Weekly averages of daily data

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 781 views
  • 6 likes
  • 2 in conversation