Tabulate row percentages by category with missing values

[shailey]

I frequently analyze survey research where I would like to calculate row percents by category for several variables. The problem arises when there are different missing values for each variable. For example, imagine 10 people answered a survey with 5 questions (Q1-Q5). Person 1 answered all 5 questions, Person 2 skipped Q3, Person 3 skipped Q2, Person 4 skipped Q1, and so on. So when I calculate the percentages for each question, I want it to be the percentage of people who answered each question, not the total number who participated in the survey. Because the missing values vary I can't set them as class variables. I can set them as var variables easily enough, and I can get the count of the 1s and the count of the non-missing values, but I can't for the life of me figure out how to get the percentage by category.

To give an example of what my data look like:

data test;

input category $ Q1 Q2 Q3 Q4 Q5;

datalines;

a 1 0 1 0 1

b 1 1 . 1 0

a 0 . 1 1 0

b . 0 1 0 1

a 1 0 1 . 0

b 0 1 0 1 0

a . 1 0 1 0

b 1 0 0 0 0

a 1 0 0 1 1

b 0 1 1 . 0

a 1 0 . 1 1

proc print;

run;

I know I'm doing something wrong, but this table has the correct counts:

proc tabulate data = test format = comma6.;

class category;

var Q1-Q5;

tables

q1-q5, (all category)* (all n);

run;

the "Sum" has the count of 1s, and the N has the count of the 1s and 0s. (i'm not exactly sure where the sum came from, which is part of why I'm sure I'm doing something wrong).

The table below is laid out exactly how I would want, except I would like to add another column for each category that has the percent by category for each of the Q1-Q5.

proc tabulate data = test format = comma6.;

class category;

var Q1-Q5;

tables Q1-Q5, (all = "All" category = "") * (all = "" n = "Total") / row = float;

keylabel sum = "1s";

run;

So for example, Q1 would be 5/8 = 63% overall, 75% for category a, and 50% for category b.

Maybe I am going about this totally the wrong way, so if anyone has any suggestions I'd really appreciate it! This comes up very frequently for me so I'd really like to find a solution.

Thanks!

[ballardw · Accepted solution]

With 0/1 coded values such as you are using N = number responses, Mean= Percent of responses

Try:

table Q1-Q5, (all = "All" category = "") * (all = "" n = "Total" mean='%'*f=percent8.1) / row = float;

[shailey]

Ahhhhh, so easy!!! Thank you so much!!