Calculate mean by ID/ Visit with multiple attempts per visit

[jenim514]

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

[PGStats · Accepted solution]

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;

[jenim514]

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;

[jenim514]

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;