Exporting results of tabulation of multiple questions in a survey battery to Excel

[Emil Alnor]

I have a lot of variables representing likert scaled question (Strongly agree, partly agree, neither agree nor disagree, partly disagree, strongly disagree, don't know, not relevant ...) from a survey. They were in a survey-battery so all the answer-categories (value labels) are the same. I wish to make a table in Excel so I can make a figure reporting the distribution of answers across the questions, that is good looking (sorry, Stata).

Currently I do this by:

putexcel set figurer, sheet(proces) modify
forval x= 18/27{
local y = 1 + ((`x'-18)*3)

collect: table () s`x', statistic(percent)
putexcel a`y' = collect
}

My code has two problems:

1) in the Excel-sheet, the first row contains the question name, second row contains the answer-categories (value labels) and third row contains the frequency-distribution (and so on for each question). It would be nice to have the answer-categories in coloumns B-H, and the question-text in rows 2-9. Something like the matrix this produces:

* Example generated by -dataex-. For more info, type help dataex
clear
input str35 var1 str14 var2 str12 var3 str26 var4 str15 var5 str17 var6 str10 var7
""                                    "Strongly agree" "partly agree" "neither agree nor disagree" "partly disagree" "strongly disagree" "don't know"
"Exporting results to Excel are easy" "10"             "30"           "20"                         "40"              "0"                 "0"        
"Stata is a good program"             "50"             "20"           "10"                         "10"              "5"                 "5"        
"Stata are good at making figures"    "20"             "20"           "0"                          "20"              "20"                "10"        
end

2) It takes a lot of time to run.

Maybe some hlep with problem 1 would also fix problem two?

My data looks something like this:

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(s18 s19 s20 s21 s22 s23 s24 s25 s26 s27)
4 5 7 3 5 3 4 5 5 4
5 6 7 6 6 5 7 7 7 5
5 5 5 5 5 5 5 5 5 5
2 3 3 5 3 4 3 3 4 5
. . . . . . . . . .
5 5 4 4 4 4 4 5 5 4
5 5 7 5 5 5 5 4 7 5
5 6 5 5 5 5 5 5 5 5
5 5 5 5 5 5 4 5 5 5
5 5 5 5 5 5 5 5 5 5
3 3 3 3 3 3 3 3 3 3
5 5 5 4 4 5 5 5 5 5
5 5 5 5 5 5 5 5 5 5
. . . . . . . . . .
6 6 6 6 6 6 6 6 6 6
4 4 7 3 4 7 . 5 5 5
. . . . . . . . . .
5 5 7 5 5 5 7 5 5 5
5 5 5 5 5 5 7 5 5 5
end