stack statistics of multiple variables in the same table

[XiaoGuaiShou]

Hello all:

  In a project, I hope to create one summary table for multiple variables. For example, I have several Questions with same answer style (level 1-4:very hard, hard, easy, very easy). My data looks like this:

id     Question_1      Question_2        Question_3

1       hard                 very hard              easy

2      very hard            hard                   hard 

............

I can easily produce a summary table for each one of those variables separately. But what I need is to stack all of them together in the same table like

variable                          Level (%percentage)

                            very hard       hard      easy      very easy

Question_1            32%             32%      32%         4%                       

Question_2            10%             32%      32%         26%  

Question_3           58%              32%      10%         0% 

......

However, I cannot come up with an easy solution with any of proc report, tabulate or freq. I know I could run multiple proc tabulate/freq, output summaries to individual dataset and merge them together. but the situation is I need to make the report all completed by SAS.

Any help would be greatly appreciated.

Thanks,

[Reeza]

Transpose (via PROC TRANSPOSE or an array) your data to a long format and then use PROC FREQ.

ID Question Answer 

1 1 1

1 2 3

1 3 5

1 4 2

2 1 3

2 2 3

2 2 5

2 4 5

You can also look at SURVEYFREQ though I suspect its the same. 

---

@XiaoGuaiShou wrote:

 I know I could run multiple proc tabulate/freq, output summaries to individual dataset and merge them together. but the situation is I need to make the report all completed by SAS.

---

Yes, this is an option that you can do WITHIN SAS as well. Not sure what you mean it must be completed by SAS.

[ballardw · Accepted solution]

Here is one way to look at this type of problem.

data have;
   infile datalines dlm=',';
   informat id $2.   Question_1  Question_2  Question_3 $10.;
   input id     Question_1 Question_2  Question_3;
datalines;
1 ,hard,very hard,easy
2 ,very hard,hard,hard 
3 ,very easy,easy,hard 
4 ,very hard,hard,easy 
5 ,very easy,hard,hard 
6 ,very easy,easy,easy
;
run; 
data want;
   set have;
   array q Question_1 - Question_3;
   do i= 1 to dim (q);
      value=q[i];
      qname= vname(q[i]);
      output;
   end;
   keep id value qname;
run;

proc tabulate data=want;
   class qname value;
   table qname='',
         value*rowpctn=''
   ;
run;

If you have label that provides nicer descriptions of your variables then you could make the length of the Qname variable longer and use qname= vlabel(q[I]);

[XiaoGuaiShou]

It is really what I want. So clever way to pivot the table. Thanks so much!!!