topic Re: Calculate mean by ID/ Visit with multiple attempts per visit in SAS Programming

Calculate mean by ID/ Visit with multiple attempts per visit

jenim514 — Sat, 11 Nov 2017 18:34:46 GMT

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

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

PGStats — Sat, 11 Nov 2017 19:55:09 GMT

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;

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

jenim514 — Sat, 11 Nov 2017 21:25:24 GMT

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;

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

jenim514 — Sat, 11 Nov 2017 21:34:30 GMT

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;