BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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?

1 ACCEPTED SOLUTION

Accepted Solutions
RahulG
Barite | Level 11

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

8 REPLIES 8
RahulG
Barite | Level 11

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
Quartz | Level 8

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
Barite | Level 11

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
Quartz | Level 8

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

RahulG
Barite | Level 11

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
Quartz | Level 8

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

RahulG
Barite | Level 11

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

starz4ever2007
Quartz | Level 8

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

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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