putexcel: loop and export to Excel

[Nick Bornschein]

Hello,

I would like to use a loop creating a lot of cross tables and export the results (Chi2...) to Excel. Therefor I set a new Excel sheet:

putexcel set "crosstabs.xlsx", sheet("cl_1a") replace
putexcel A1=("variable") B1=("label") C1=("Chi2") D1=("p (Chi)") E1=("Fisher exakt") F1=("Cramver V")

Now I'd like to lop through 540 variables and save the crosstab results to Excel, where I need the variable names in column A and the variable labels in column B, all other results following in the columns C to F. The loop beneath provides all I need exept the variable names and labels and of course the change in every row saving the results of each of the cross tabulation. Any idea how to manage that?

foreach x of varlist x1-x540 {
tabulate cl_1a `x', chi2 exact V
putexcel A2=
putexcel B2=
putexcel C2=(r(chi2))
putexcel D2=(r(p))
putexcel E2=(r(p_exact))
putexcel F2=(r(CramersV))
}

Best
-Nick

[Joe Canner]

Nick,

How about:

local row=2
foreach x of varlist x1-x540 {
tabulate cl_1a `x', chi2 exact V
local varlabel : var label `x'
putexcel A`row'=("`x'")
putexcel B`row'=("`varlabel'")
putexcel C`row'=(r(chi2))
putexcel D`row'=(r(p))
putexcel E`row'=(r(p_exact))
putexcel F`row'=(r(CramersV))
local ++row
}

Regards,
Joe

[Nick Bornschein]

That worked perfect, thanks.

Maybe some last thing to make it brilliant:

I do not only calculate the statistics for the crosstab with the variable cl_1a, also with other 31 variables (cl_1b...). Is there any way to integrate these variables (in total 32) in the loop to loop though it completely and set a new Excel sheet for every new variable I crosstab (sheet for cl_1b...)?
The putexcel command I use for every loop of the 32 variables I crosstab is:

putexcel set "crosstab.xlsx", sheet("cl_1a") modify
putexcel A1=("variable") B1=("Label") C1=("Chi2") D1=("p (Chi)") E1=("Fisher exakt") F1=("Cramver V")

-Nick