How to export matrices to Excel with if condition

[Aditi EPRI]

Hello users, need advice to exporting tables to Excel using matrices.

I have run hundreds of crosstabs with chi-square tests (ta var1 var2, chi2) and exporting them through matrices. Matrix for each cross-tab has 3 columns (N, chi value, p-value). I want to export the result of a cross-tab only if the test is significant i.e. the p-val <=0.05. Please advise if there is a solution.

Below is the loop I have setup to export the values.

gl model q30a q30b q30c q30d q30e



foreach i in $model{

putexcel set "$path_output\Section 3 - Q24.xlsx", sheet("`i'") modify


clear matrix
matrix Table =J(12,3,.)
matrix rownames Table = q24a q24b q24c q24d
matrix colnames Table = N chisq p

foreach var of varlist q24a q24b q24c q24d {
    
    ta `i' `var', chi2
    matrix Table[rownumb(Table, "`var'"),1] = r(N)
    matrix Table[rownumb(Table, "`var'"),2] = r(chi2)
    matrix Table[rownumb(Table, "`var'"),3] = r(p)
    
    
}

putexcel A1= matrix(Table), names 
    
    
}

Both commands, matrix and putexcel do not support if conditions. However, it is important that i restrict the export only to significant results.

Please help!

[Hemanshu Kumar]

I believe you need the if command, not the if condition. See

help ifcmd

[Aditi EPRI]

Please advise how I can give this command to Stata:

  
 foreach var of varlist q24a q24b q24c q24d {          
ta `i' `var', chi2    
matrix Table[rownumb(Table, "`var'"),1] = r(N)    
matrix Table[rownumb(Table, "`var'"),2] = r(chi2)    
matrix Table[rownumb(Table, "`var'"),3] = r(p)           }  
if matrix Table[rownumb(Table, "`var'"),3] = r(p) >=0.05   putexcel A1= matrix(Table), names  
else {continue loop for next variable}           }

[Hemanshu Kumar]

Could you clarify what you want?

Your matrix has four rows, one for each variable q24a-q24d. Each row has its own p-value in column 3. Do you want the matrix to have that full row missing when the p-value for that variable is above 0.05? Or something else?

What happens when all four p-values are above 0.05? Do you want no table exported at all, or something else?

[Aditi EPRI]

Hi Hemanshu, thanks.

If its simpler i just want the p-val blank if it is over 0.05

[Aditi EPRI]

if all over 0.05 i want no table exported at all

[Hemanshu Kumar]

Try this perhaps? (This code is untested, since no data extract was provided. Also, I am assuming the rest of your original code works. It is best to provide a data extract using -dataex- to allow for efficient problem-solving and troubleshooting. See the Statalist FAQ, especially part 12)

gl model q30a q30b q30c q30d q30e

foreach i in $model {
    putexcel set "$path_output/Section 3 - Q24.xlsx", sheet("`i'") modify

    clear matrix
    matrix Table =J(12,3,.)
    matrix rownames Table = q24a q24b q24c q24d
    matrix colnames Table = N chisq p

    local minpval 1
    
    foreach var of varlist q24a q24b q24c q24d {
        ta `i' `var', chi2
        matrix Table[rownumb(Table, "`var'"),1] = r(N)
        matrix Table[rownumb(Table, "`var'"),2] = r(chi2)
        if r(p) < 0.05 matrix Table[rownumb(Table, "`var'"),3] = r(p)
        local minpval = min(`minpval',r(p))
    }
    if `minpval' < 0.05 putexcel A1 = matrix(Table), names   
}

[Aditi EPRI]

You're my hero! thanks a lot that worked.

Could i also code it to work that it only makes the tables if N>=30

  
 gl model q30a q30b q30c q30d q30e  
foreach i in $model {     putexcel set "$path_output/Section 3 - Q24.xlsx", sheet("`i'") modify      
clear matrix    
matrix Table =J(12,3,.)    
matrix rownames Table = q24a q24b q24c q24d    
matrix colnames Table = N chisq p      
local minpval 1          
foreach var of varlist q24a q24b q24c q24d
{        

if sum(`var')>=30 ta `i' `var', chi2        
matrix Table[rownumb(Table, "`var'"),1] = r(N)        
matrix Table[rownumb(Table, "`var'"),2] = r(chi2)        
if r(p) < 0.05 matrix Table[rownumb(Table, "`var'"),3] = r(p)        
local minpval = min(`minpval',r(p))     }    
if `minpval' < 0.05
putexcel A1 = matrix(Table), names  

else {  go to next var }    
}

[Aditi EPRI]

Hi i tried this code and it seems to work ..

gl model q30a q30b q30c q30d q30e q30f q30g
foreach i in $model {
    putexcel set "$path_output/Section 3 - Q24.xlsx", sheet("`i'") modify

    clear matrix
    matrix Table =J(4,3,.)
    matrix rownames Table = q24a q24b q24c q24d
    matrix colnames Table = N chisq p

    local minpval 1
    
    foreach var of varlist q24a q24b q24c q24d q24e q24f q24g q24h q24i q24j q24k q24l {
            
        total `var'
        matrix xyz = r(table)
        scalar test = xyz[1,1]
        if xyz[1,1]>=30 ta `i' `var' , chi2
        matrix Table[rownumb(Table, "`var'"),1] = r(N)
        matrix Table[rownumb(Table, "`var'"),2] = r(chi2)
        if r(p) < 0.05 matrix Table[rownumb(Table, "`var'"),3] = r(p)
        local minpval = min(`minpval',r(p))
        //local minsum = sum(`minsum',r(N))
        
    }
    if `minpval' < 0.05 putexcel A1 = matrix(Table), names
}

[Hemanshu Kumar]

I'm surprised you say that is working correctly. The command

total `var'

will put the sum of all values of `var' in your xyz[1,1], whereas I think you want the count of observations in the tabulation. Perhaps you need something like this:

local model q30a q30b q30c q30d q30e q30f q30g
local vars q24a q24b q24c q24d q24e q24f q24g q24h q24i q24j q24k q24l

foreach i in `model' {
    putexcel set "$path_output/Section 3 - Q24.xlsx", sheet("`i'") modify

    clear matrix
    matrix Table =J(4,3,.)
    matrix rownames Table = `vars'
    matrix colnames Table = N chisq p

    local minpval 1
    local maxn 0
    
    foreach var of varlist `vars' {
            
        ta `i' `var' , chi2
        local maxn = max(`maxn',r(N))
        
        if r(N) >= 30 {
            matrix Table[rownumb(Table, "`var'"),1] = r(N)
            matrix Table[rownumb(Table, "`var'"),2] = r(chi2)
            if r(p) < 0.05 matrix Table[rownumb(Table, "`var'"),3] = r(p)
            local minpval = min(`minpval',r(p))
        }
    }
    
    if `minpval' < 0.05 & `maxn' >= 30 putexcel A1 = matrix(Table), names
}

[Aditi EPRI]

Hi Hemanshu, I did not clarify that `var' are binary variables so total would work just fine .. the way i tried using sum gave me wrong values. In your code we get the N of the sample used in the crosstab. However, I only want a crosstab if var>30