topic Re: stack statistics of multiple variables in the same table in SAS Programming

stack statistics of multiple variables in the same table

XiaoGuaiShou — Wed, 11 Oct 2017 21:57:48 GMT

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,

Re: stack statistics of multiple variables in the same table

Reeza — Wed, 11 Oct 2017 22:23:29 GMT

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.

Re: stack statistics of multiple variables in the same table

ballardw — Wed, 11 Oct 2017 22:31:05 GMT

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]);

Re: stack statistics of multiple variables in the same table

XiaoGuaiShou — Mon, 30 Oct 2017 12:57:36 GMT

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