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 |
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;
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;
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.