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!
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;
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;
Ahhhhh, so easy!!! Thank you so much!!
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 16. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: