Dear all,

Seeking some advice on how to export a specific format of output into Excel, from tabulated data. I've had do this before but after a bit of struggle with the code have ended up copying the data across manually. I come across this issue often enough I thought it was about time to see if anyone has any wise solutons.

We have a large survey/audit, with multiple binary questions. Each of these are tabulated against demographic variables (age, sex, ethnicity, etc). For instance, one tabulation below on a subset of data looking at homelessness and age. Note we also have one variable to record if this question was asked:

. tab _h_homeless _agecat if _h_homeless_asked==1

Have they |
experience |
d |
homelessne |
ss in last | Age
12 months | 11 to <15 15 to 19 | Total
-----------+----------------------+----------
No | 16 17 | 33
Yes | 3 8 | 11
-----------+----------------------+----------
Total | 19 25 | 44


The output we are trying to export from this tabulation is in the table below. Note how column D onwards are intended to express an out put of "n/column total (%)".
Variable Label Number asked Positive Age Sex
11-15 yrs 15-19 years Male Female
Have they experienced homelessness in last 12 months 44 11 3/19 (15.8) 8/25 (32.0) ... ...
… next question
… next question

With help from Stata technical support, I have developed code to set up the table (column headings and variable labels in each row of column A) and can export results in the 'Number asked' and 'Positive' columns using -putexcel-

My challenge comes in exporting the desired cell format for other results and how to loop this over multiple cross tabulations.

The following will generate the output I desire:

tab _h_homeless _agecat if _h_homeless_asked==1, col matcell(X)

mata : st_matrix("coltot", colsum(st_matrix("X")))
mata : st_matrix("rowtot", rowsum(st_matrix("X")))

matrix coltotal = J(1, rowsof(X), 1) * X
matrix rowtotal = X * J(colsof(X), 1, 1)

mat li coltot
mat li X
local j = colsof(coltotal)

local a = colsof(X)
local b = colsof(X)

forval aa = 1/`a' {
local A = X[2,`aa']
local B = coltot[1,`aa']
local C = string(X[2,`aa']/coltot[1,`aa']*100, "%9.1f")
local D `" `A' "/" `B' " " "(" `C' ")" "'
display `D'
}

Gives the following output in Stata viewer:
3/19 (15.8)
8/25 (32)

Any suggestions for how to use -putexcel- to export these into the correct cells in Excel? And then how to loop through multiple variables?
Despite the bit of mata code above (taken from a helpful statalist post!) I am not proficient in mata.

Kind thanks,
Nick