Creating One Table From Multiple (Check All That Apply) Variable

Accepted Solution Solved
Reply
Contributor
Posts: 26
Accepted Solution

Creating One Table From Multiple (Check All That Apply) Variable

Thank you for taking the time to look over my post.

I have a rather large dataset with numerous check all that apply questions.

For example, I'll use the race question:

Check all that apply:

White

Black

Asian

Pacific Islander

Other

Each of these options have their own separate variable name (white=q7r1, black=q7r2, asian=q7r3, PI=q7r4, and other=q7r5).

When running Proc freq, these variable come out in their own tables, respectively.  

How would i create a table where the values (N, and %) are listed in one,e.g:

What is your race?

white 4 (35%)

black 7 (38%)

etc....

Thank you again and have a great day!


Accepted Solutions
Solution
‎03-13-2014 03:44 PM
Super User
Posts: 5,096

Re: Creating One Table From Multiple (Check All That Apply) Variable

OK.  There's no way to do this (that I know of) without manipulating the data a bit.  For example:

data want / view=want;

   set have;

   length race $ 16;

   keep race;

   if q7r1 then do;

      race='White';

      output;

   end;

   if q7r2 then do;

      race='Black';

      output;

   end;

   ...

   if q7r5 then do;

      race='Other';

      output;

   end;

run;

proc freq data=want;

   tables race;

run;

Good luck.

View solution in original post


All Replies
Super User
Posts: 5,096

Re: Creating One Table From Multiple (Check All That Apply) Variable

If a respondent checked 2 boxes, how would you like to count that?  As a full "1" for both races?  As 0.5 for each?  What if a respondent checked 3 boxes?  The programming won't be terribly hard, but defining the problem has to be the first step.

Contributor
Posts: 26

Re: Creating One Table From Multiple (Check All That Apply) Variable

Sorry for not being clear, but yes, if a respondent checked 2 boxes, each would be counted as '1'.

thank you for your help

Solution
‎03-13-2014 03:44 PM
Super User
Posts: 5,096

Re: Creating One Table From Multiple (Check All That Apply) Variable

OK.  There's no way to do this (that I know of) without manipulating the data a bit.  For example:

data want / view=want;

   set have;

   length race $ 16;

   keep race;

   if q7r1 then do;

      race='White';

      output;

   end;

   if q7r2 then do;

      race='Black';

      output;

   end;

   ...

   if q7r5 then do;

      race='Other';

      output;

   end;

run;

proc freq data=want;

   tables race;

run;

Good luck.

Contributor
Posts: 26

Re: Creating One Table From Multiple (Check All That Apply) Variable

thank you so much, that was amazing!

Respected Advisor
Posts: 3,777

Re: Creating One Table From Multiple (Check All That Apply) Variable

If the response is coded 0/1 it might be easier to just take sum=count mean=percent and use proc means to make a data set of percents.  Do pay any attention to the PROC PLAN I just used it to create some sample data.  A data step would have been simpler but what the hay.

proc plan seed=9876;
  
factors id=35  ordered
      q7r1=
1 of 2
      q7r2=
1 of 4
      q7r3=
1 of 5
      q7r4=
1 of 4
      q7r5=
1 of 5

      q8r1=
1 of 2
      q8r2=
1 of 3
      q8r3=
1 of 4
      q8r4=
1 of 3

      /
noprint;
  
output out=q
      q7r1
nvals=(1 0) q7r2 nvals=(1 0 0 0) q7r3 nvals=(1 0 0 0 0) q7r4 nvals=(1 0 0 0) q7r5 nvals=(1 0 0 0 0)
      q8r1
nvals=(1 0) q8r2 nvals=(1 0 0) q8r3   nvals=(1 0 0 0) q8r4   nvals=(1 0 0)
      ;
   run;
proc print;
  
run;
proc means stackods data=q n mean sum;
  
var q:;
   ods select none;
  
ods output Summary=Summary(rename=(mean=pct sum=count));   
  
run;
ods select all;
proc print;
  
format pct percent8.2 count 8.;
  
run;
  
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 315 views
  • 3 likes
  • 3 in conversation