loop with crosstab and no repeating

[Nick Bornschein]

Hi,

I'm using a nice syntax to create crosstabels for a lot of crosstabs and exporting the results (Chi, p...) to excel:

--

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

global indepvars a b c d e

local row=2
foreach x of varlist $indepvars {
tabulate group `x', chi2 exact(2) V nofreq
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
}
--

Until now I switched the variable which my global list has to be crosstabed with, so in the above syntax it is "group" and I created an extra excel sheet for every change of that. Now I have a different situation. I have a new list auf variables

global vars f g h k l m

I want to use in the way that I can crosstab every possible crosstab between these variable of $vars like that:

--

global vars f g h k l m

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

local row=2
foreach x of varlist $vars {
tabulate $vars `x', chi2 exact(2) V nofreq
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
}
--

Bold marked are the placed which need to be changed I think: first I just want to calculate every possible crosstab, but no repeated crosstab like "f with g" and "g with f", and second I need an Excel sheet for every single crosstab I produce.
Is that possible? Would be great to get an answer.

Thanks a lot.
-Nick

[Clyde Schechter]

So, I think you need a slightly different loop structure. You have a single variable list $vars. I have a strong preference for local macros, so I will refer to `vars' instead, but you should feel free to persist with your global if you like.

local n_vars: word count `vars'
forvalues j = 1/`n_vars' {
    local jvar: word `j' of `vars'
    putexcel set "crosstabs.xlsx", sheet("`jvar'") modify
    local row = 2
    forvalues k = `=`j'+1'/`n_vars' {
        local kvar: word `k' of `vars'
        tabulate `jvar' `kvar', chi2 exact(2) V nofreq
        putexcel A`row' = ("`kvar'")
        putexcel B`row' = ("`:var label `kvar''")
        putexcel C`row' = (`r(chi2)') // etc.  I WON'T WRITE OUT THE REST OF THE putexcel'S
        local ++row
    }
}

will, I think, solve both your problems. You will get no "f with g" and "g with f" repeats. And you will have one single worksheet for each variable, not one for each combination. I have not tested this code, but I'm pretty confident of it.

Hope this is what you were looking for.

[Nick Bornschein]

That worked GREAT. Thanks a lot.