DATA Step, Macro, Functions and more

stack statistics of multiple variables in the same table

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

stack statistics of multiple variables in the same table

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,


Accepted Solutions
Solution
3 weeks ago
Super User
Posts: 11,343

Re: stack statistics of multiple variables in the same table

Posted in reply to XiaoGuaiShou

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

 

View solution in original post


All Replies
Super User
Posts: 19,855

Re: stack statistics of multiple variables in the same table

Posted in reply to XiaoGuaiShou

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.

Solution
3 weeks ago
Super User
Posts: 11,343

Re: stack statistics of multiple variables in the same table

Posted in reply to XiaoGuaiShou

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

 

Occasional Contributor
Posts: 11

Re: stack statistics of multiple variables in the same table

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

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 199 views
  • 4 likes
  • 3 in conversation