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,
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]);
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.
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]);
It is really what I want. So clever way to pivot the table. Thanks so much!!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.