Calculate percent of days completed for study

[393310]

ID	Group	Week	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
4	B	1	2	2	2	2	2	2	2
4	B	2	2	2	2	2	2	2	2
4	B	3	2	2	2	2	2	2	2
4	B	4	2	2	2	2	2	2	2
4	B	5	2	2	2	2	2	2	2
4	B	6	2	2	2	2	2	2	2
4	B	7	2	2	2	2	2	2	2
4	B	8	2	2	2	2	2	2	2

Hi, I am trying to calculate the percent of days that have "2" for the total of the 8 weeks by Group. This is what my data would be similar to. Is anyone able to help me with this?

[ballardw]

Your example data is kind of flaky in that every day has a value of 2 and there is only one group. So any percentage would be 100.

Does group repeat values for different  ID? If so, do you expect the count/percent to only consider group or is it group within ID?

Do any of your days ever have missing values?

Can you show what you expect for output? That can make a big difference on how to approach this.

Here's one way with a data set that has values other than 2.

data have;
  input ID $ 	Group $ 	Week 	day1-day7;
datalines;
4 	B 	1 	2 	2 	2 	2 	2 	2 	2
4 	B 	2 	1 	2 	2 	2 	2 	2 	2
4 	B 	3 	2 	1 	1 	2 	2 	2 	2
4 	B 	4 	2 	2 	2 	1 	2 	2 	2
4 	B 	5 	2 	2 	2 	2 	1 	1 	2
4 	B 	6 	2 	2 	2 	2 	2 	2 	2
4 	B 	7 	2 	2 	2 	2 	2 	1 	2
4 	B 	8 	2 	1 	1 	1 	1 	2 	2
;
proc transpose data=have out=trans;
   by id group week;
   var day1-day7;
run;

proc freq data=trans;
   tables group*col1/list;
run;

If you have missing values and expect them to be counted in the denominator then use the MISSING option in that tables statement.

But this assumes you want the GROUP counted across all ID if there are any. If not then add ID to the tables statement.

Warning: If you have repeats of ID Group Week value combinations this won't work because the transpose step would make more than one output Col variable.

[Ksharp]

data have;
  input ID $  Group $  Week  day1-day7;
datalines;
4  B  1  2  2  2  2  2  2  2
4  B  2  1  2  2  2  2  2  2
4  B  3  2  1  1  2  2  2  2
4  B  4  2  2  2  1  2  2  2
4  B  5  2  2  2  2  1  1  2
4  B  6  2  2  2  2  2  2  2
4  B  7  2  2  2  2  2  1  2
4  B  8  2  1  1  1  1  2  2
;

proc sql;
create table want as
select group,
sum(sum(day1=2),sum(day2=2),sum(day3=2),sum(day4=2),sum(day5=2),sum(day6=2),sum(day7=2)) /
sum(count(day1),count(day2),count(day3),count(day4),count(day5),count(day6),count(day7)) 
as percent format=percent8.2
 from have 
  group by group ;
quit;