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?
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.
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;
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...
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.
where would you incorporate "having count (*) >3". I would only want the means and counts for weeks that had more than three scores?
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
thank you for all your help! i appreciate it 🙂 it worked for me! and thank you for the quick replies!
You should have marked solution to my reply instead on to your own post
sorry about that...changed the solution back to you!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.