topic Weekly averages of daily data in SAS Programming

Weekly averages of daily data

starz4ever2007 — Tue, 26 Jul 2016 03:35:11 GMT

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?

Re: Weekly averages of daily data

RahulG — Tue, 26 Jul 2016 03:40:33 GMT

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;

Re: Weekly averages of daily data

starz4ever2007 — Tue, 26 Jul 2016 04:19:07 GMT

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

Re: Weekly averages of daily data

RahulG — Tue, 26 Jul 2016 04:46:44 GMT

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.

Re: Weekly averages of daily data

starz4ever2007 — Tue, 26 Jul 2016 05:06:58 GMT

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

Re: Weekly averages of daily data

RahulG — Tue, 26 Jul 2016 05:09:54 GMT

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

Re: Weekly averages of daily data

starz4ever2007 — Tue, 26 Jul 2016 05:12:03 GMT

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

Re: Weekly averages of daily data

RahulG — Tue, 26 Jul 2016 05:13:57 GMT

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

Re: Weekly averages of daily data

starz4ever2007 — Tue, 26 Jul 2016 05:34:12 GMT

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