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;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.