turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Tabulate row percentages by category with missing ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-14-2015 03:00 PM

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!

Accepted Solutions

Solution

04-14-2015
03:29 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to shailey

04-14-2015 03:29 PM

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;

All Replies

Solution

04-14-2015
03:29 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to shailey

04-14-2015 03:29 PM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to shailey

04-14-2015 03:34 PM

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