Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Dynamically assigning putexcel row and column names

    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.

    Code:
    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!
    Last edited by Will Sankey; 28 Jan 2015, 17:14.

  • #2
    I think you want something like:

    Code:
    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-.
    Last edited by Roberto Ferrer; 28 Jan 2015, 23:07.
    You should:

    1. Read the FAQ carefully.

    2. "Say exactly what you typed and exactly what Stata typed (or did) in response. N.B. exactly!"

    3. Describe your dataset. Use list to list data when you are doing so. Use input to type in your own dataset fragment that others can experiment with.

    4. Use the advanced editing options to appropriately format quotes, data, code and Stata output. The advanced options can be toggled on/off using the A button in the top right corner of the text editor.

    Comment


    • #3
      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):

      Code:
      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 
      }

      Comment

      Working...
      X