Dynamically assigning putexcel row and column names

[Will Sankey]

Hi,

In the following example I am running a twoway crosstab and want to output to excel (using the Stata 13 putexcel command) the -levelsof- values from each variable for the row and column names. I've tried a few different approaches; saving the levelsof values to a local macro, reformatting the matrix itself, but I am relatively new to matrix language and could use some guidance.

foreach i of numlist 11  {
    /*I have 2011 and 2012 data that I merge to perform the crosstab, not relevant for this question*/
    local j=`i' + 1
    use transitionmatrix_data_20`i', clear
        merge 1:1 contractnumber using transitionmatrix_data_20`j'
    
    tab overall_num_20`i' overall_num_20`j', matrow(freq) matcell(stars)
        *levelsof overall_num_2012, local(test) /*Works, but not sure how to putexcel into columnames..*/
    matrix list stars
    matrix list freq
    
    *Attempting to use the local macro "test" for column names..
    putexcel A1=(`test') A2=matrix(freq) B2=matrix(stars) using tabtest4

}

And here's what the matrix stars looks like in the console:

c1 c2 c3 c4 c5 c6 c7 c8 c9 c10
r1 0 24 16 0 1 6 5 3 0 0
r2 0 0 57 0 8 14 9 1 1 1
r3 2 0 0 0 0 0 0 0 0 0
r4 0 0 2 4 25 12 1 0 0 0
r5 0 0 1 2 31 89 40 2 0 0
r6 0 0 0 0 0 21 53 19 7 0
r7 0 0 0 0 0 1 10 18 12 0
r8 0 0 0 0 0 1 0 7 19 6
r9 0 0 0 0 0 0 0 1 0 2


Thanks for any input!

[Roberto Ferrer]

I think you want something like:

clear
set more off

*----- example data -----

webuse citytemp2
tabulate region agecat, matcell(allfreq)

matrix list allfreq

*----- levels only -----

levelsof region, local(reg)
levelsof agecat, local(age)

matrix rownames allfreq = `reg'
matrix colnames allfreq = `age'

matrix list allfreq

*----- labels too -----

foreach var of varlist region agecat {
    
    // retrieve levels of each variable
    levelsof `var', local(`var'_levels)
    
    // create local with all corresponding value labels
    foreach val of local `var'_levels {
        local `var'vl ``var'vl' `"`: label (`var') `val''"'
    }

}

matrix rownames allfreq = `regionvl'
matrix colnames allfreq = `agecatvl'

matrix list allfreq

The idea is to rename matrix row and column names before the export. This uses a macro extended function to extract value labels. See -help extended_fcn-.

[Will Sankey]

Thank you Roberto, with one key caveat this worked perfectly. The caveat being that I was receiving the '3200 Noncoformability' error when running this code because I had some missing values. For those of you looking at this post in the future and obtaining this error, you should do something similar to the following (note the missings):

foreach i of numlist 11 12 13 14 {
    /*I have 2011 and 2012 data that I merge to perform the crosstab, not relevant for this question*/
    local j=`i' + 1
    use transitionmatrix_data_20`i', clear
        merge 1:1 contractnumber using transitionmatrix_data_20`j'
    
    tab overall_num_20`i' overall_num_20`j', missing  matcell(stars)
        levelsof overall_num_20`i', missing local(down)
        levelsof overall_num_20`j', missing local(across)

    matrix rownames stars = `down'
    matrix colnames stars = `across'

        matrix list stars

    
    *Attempting to use the local macro "test" for column names..
    putexcel  A3=("Label1") C1=("Label2") B2=matrix(stars, names) using transitionmatrices_012915, sheet("20`i'_20`j'") modify
clear matrix 
}