DATA Step, Macro, Functions and more

Calculate mean by ID/ Visit with multiple attempts per visit

Accepted Solution Solved
Reply
Regular Contributor
Posts: 181
Accepted Solution

Calculate mean by ID/ Visit with multiple attempts per visit

Hi Everyone,

 

I fell like I losing sleep on how to get this output set up!  I have a dataset that has possible values of 0/1.  I have multiple possible encounters with the same ID over a single visit (e.g. multiple attempts on a questionnaire during a single visit).  So I need to sum the values by ID and VIsit#  (new variable sum_visits) and then get the means from those sum_visits BY visit#.  I think this can be done with proc tabulate but I can't set it up right.  I keep getting Mean=1 so it isn't summing by ID/VIsit first.  Any help is greatly appreciated!!!

 

I have data that looks like this

 

ID Score Visit
A 1 1
A 0 1
A 1 1
A 1 1
A 1 2
A 0 2
A 1 2
A 1 2
B 0 1
B 1 1
B 1 1
B 0 1
B 1 2
B 1 2
B 1 2
B 1 2

 

Would like results that give something like this (but doesn't have to be  set up the way (just an example of my calculations).

ID Score Visit Sum_Visits    
A 1 1      
A 0 1      
A 1 1      
A 1 1 3    
A 1 2      
A 0 2      
A 1 2      
A 1 2 3    
B 0 1      
B 1 1      
B 1 1      
B 0 1 2    
B 1 2      
B 1 2      
B 1 2      
B 1 2 4    
           
           
        Sum Vist 1* ID 5
        Sum Visit2 *ID 7
        Mean Visit 1 5/2= 2.5
        Mean Visit 2 7/2=3.5

Accepted Solutions
Solution
‎11-11-2017 04:35 PM
Esteemed Advisor
Posts: 5,474

Re: Calculate mean by ID/ Visit with multiple attempts per visit

SQL is a natural for this:

 

proc sql;
create table want as
select visit, mean(totalScore) as meanTotalScore
from
   (select id, visit, sum(score) as totalScore
    from have
    group by id, visit)
group by visit;
select * from want;
quit;
PG

View solution in original post


All Replies
Solution
‎11-11-2017 04:35 PM
Esteemed Advisor
Posts: 5,474

Re: Calculate mean by ID/ Visit with multiple attempts per visit

SQL is a natural for this:

 

proc sql;
create table want as
select visit, mean(totalScore) as meanTotalScore
from
   (select id, visit, sum(score) as totalScore
    from have
    group by id, visit)
group by visit;
select * from want;
quit;
PG
Regular Contributor
Posts: 181

Re: Calculate mean by ID/ Visit with multiple attempts per visit

This is awesome!! I'm trying to add just one more new variable (not in the have dataset)...with a specific value of '004',=. Can you show me where this goes? Here is my attempt...didn't work.

proc sql;
create table sep_004 as
select visit, mean (totalscore) as mean, max(totalscore) as max, min(totalscore) as min,
std(totalscore) as std, nmiss(totalscore) as nmiss, n(totalscore) as n
from
(select PID, visit, sum(Q1) as totalscore
from sep004
group by PID, visit)
group by visit;
select * from sep_test;
Study='004';
quit;
Regular Contributor
Posts: 181

Re: Calculate mean by ID/ Visit with multiple attempts per visit

NVMD! Got it!

proc sql;
create table sep_005 as
select visit, mean (totalscore) as mean, max(totalscore) as max, min(totalscore) as min,
std(totalscore) as std, nmiss(totalscore) as nmiss, n(totalscore) as n, ('005') as Study
from
(select PID, visit, sum(Q1) as totalscore
from sep005
group by PID, visit)
group by visit;
select * from sep_005;
quit;
☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 167 views
  • 1 like
  • 2 in conversation