putexcel: get values of categories (regression analysis)

[Nick Bornschein]

Hi,

I'm calculation a regression: reg dep c.age ib1.house ib1.hotel

Then I use a putexcel which works perfect, but instead of getting "4.house" as the cell text I would like to get the value label of house 4, which is "old house" (value 4). I think it's the last line of my command which I need to change, but don't know how or if that's all I need.

putexcel set "results.xls", sheet("all") replace
putexcel A1=("Number of obs") B1=(e(N))
putexcel A2=("F") B2=(e(F))
putexcel A3=("Prob > F") B3=(Ftail(e(df_m), e(df_r), e(F)))
putexcel A4=("R-squared") B4=(e(r2))
putexcel A5=("Adj R-squared") B5=(e(r2_a))
matrix a = r(table)'
matrix a = a[., 1..6]
putexcel A7=matrix(a, names)

[Isaac Maddow-Zimet]

Hi Nick,

The problem is that the rownames stored in r(table) after a regression don't have value labels for factor variables. One way I've found to get around this is to write a little loop -- I'm not sure this is the best way, or the most elegant, but it should work. Note that I've moved your matrix over a column, and taken out (, names) because we are going to export the row names manually.

putexcel B7=matrix(a)
local myrownames: rownames a                      // extract the current rownames in your matrix
local startrow=7                                             // this will be the first row of your matrix
foreach rowname of local myrownames {         // for each rowname
tokenize `rowname', parse(".")                        // parse by "."
if "`2'"!="." {                                                  // if the variable is continuous (not a factor variable)
local myrowname `1'                                     // set the rowname to the name of the variable
}
else {                                                          // otherwise
local `myvar' `3'                                           // store variable name in myvar
tokenize `1', parse("b")                                 // parse on b in case it's the reference category
*(you'll run into trouble here if you have categories omitted due to collinearity though)

local myrowname: label `myvar' `1'               // assign the value label of that category (now stored in `1') to myrowname
}
putexcel A`startrow'=("`myrowname'")          // export the row name
local startrow = `startrow' + 1                     // advance the row by 1
}

Let me know if this works -- I haven't tested this specific code, but have done something similar in some of my programs.

Isaac

[Nick Bornschein]

Hi Isaac,

thanks for your answer. Adapting your code, I get the error message "1 invalid name", the Excel file is written but now completely without any kind of label value exept the first one for the continous variable.

[Isaac Maddow-Zimet]

Hi Nick,

Sorry about that -- I had a small typo in line 10 of the code. See the corrected version below, which should now work (I tested it on my end this time).

putexcel B7=matrix(a)
local myrownames: rownames a                      // extract the current rownames in your matrix
local startrow=7                                             // this will be the first row of your matrix
foreach rowname of local myrownames {         // for each rowname
tokenize `rowname', parse(".")                        // parse by "."
if "`2'"!="." {                                                  // if the variable is continuous (not a factor variable)
local myrowname `1'                                     // set the rowname to the name of the variable
}
else {                                                          // otherwise
local myvar `3'                                           // store variable name in myvar
tokenize `1', parse("b")                                 // parse on b in case it's the reference category
*(you'll run into trouble here if you have categories omitted due to collinearity though)

local myrowname: label `myvar' `1'               // assign the value label of that category (now stored in `1') to myrowname
}
putexcel A`startrow'=("`myrowname'")          // export the row name
local startrow = `startrow' + 1                     // advance the row by 1
}

Thanks!
Isaac

[Nick Bornschein]

Hi Isaac,
it worked perfect for one of my catgegorial variables in my regression, but not for the others (there I can read 1,2,3...). Could there be any difference in the kind of categorical variable. All my categorical variables have standard value labels, I see no difference betweens the variables (some type...).

-Nick

[Isaac Maddow-Zimet]

Hi Nick,

I'm not sure what could be causing that, if all the categorical variables have value labels (and all are specified as factor variables in your regression). Would it be possible to post more information about the variables that are causing problems (or even attach an extract of your dataset)?

-Isaac

[Nick Bornschein]

Hi Isaac,

I have prepared an example dataset from my original data, but now I can't upload it, so here is the link to my dataset and do-file:

https://onedrive.live.com/redir?resi...5D281%21125042

After preparing my values with new labels, there is no value name in Excel using the syntax that worked before with one variable (var7 --> I justed changed the name and the value labels for the example). So it has to do with the labels... Really strange.

Thanks
-Nick


PS. Due to the weekend I will be back on Tuesday, wish you a great one. I hope the problem can be fixed.

[Isaac Maddow-Zimet]

Hi Nick,

I think I know what the problem is -- my code assumes that your value label is named the same thing as your variable. Sorry about that -- I typically do this in my code, and forgot to mention it.

This can be fixed by making just a slight modification in the code -- see below:

putexcel B7=matrix(a)
local myrownames: rownames a
local startrow=7
foreach rowname of local myrownames {
        tokenize `rowname', parse(".")
        if "`2'"!="." {
            local myrowname `1'
            }
        else {
            local myvar `3'
            tokenize `1', parse("b")
            *(Probleme hier dann wenn collin. auftritt)
            local labelvar: value label `myvar'
            local myrowname: label `labelvar' `1'
            }
        putexcel A`startrow'=("`myrowname'")
        local startrow = `startrow' + 1
}

[Nick Bornschein]

Sorry, I have to change my massage: I have seen that when I use another base for my categorical variables, all results are mixed and wrong. The syntax does only work if you use the first category as the reference, but in my case I use other reference caregories. Any idea how to solve that?

[Isaac Maddow-Zimet]

Hi Nick,

The code should work no matter the reference category -- are you using -fvset base- to change it, or some other method?

I just tried out setting your reference category to 2 for var2, and it worked fine -- see code below:

fvset base 2 var2
reg depvar c.var1 i.var2 c.var3 c.var4 i.var5 i.var6 i.var7
putexcel set "results.xls", sheet("all") replace
putexcel A1=("Number of obs") B1=(e(N))
putexcel A2=("F") B2=(e(F))
putexcel A3=("Prob > F") B3=(Ftail(e(df_m), e(df_r), e(F)))
putexcel A4=("R-squared") B4=(e(r2))
putexcel A5=("Adj R-squared") B5=(e(r2_a))
matrix a = r(table)'
matrix a = a[., 1..6]
putexcel B7=matrix(a)
local myrownames: rownames a
local startrow=7
foreach rowname of local myrownames {
        tokenize `rowname', parse(".")
        if "`2'"!="." {
            local myrowname `1'
            }
        else {
            local myvar `3'
            tokenize `1', parse("b")
            *(Probleme hier dann wenn collin. auftritt)
            local labelvar: value label `myvar'
            local myrowname: label `labelvar' `1'
            }
        putexcel A`startrow'=("`myrowname'")
        local startrow = `startrow' + 1
}

Just as an FYI -- it's a bit easier on my end to debug if you post your code, as well as the error message you received -- otherwise I have to guess what the problem might be.

Thanks,
Isaac

[Nick Bornschein]

Hello Isaac,

I did not use fvset, instead I used i2.var within the regression and that was the problem. Using "fvset base"...now it works. Thank you very much!!

[Isaac Maddow-Zimet]

Great! Thanks for closing out the thread.

[Vivek Gupta]

Hello Isaac, Nick
I came across this post while trying to achieve something similar after estout. However, it was failing if the factor variable did not have any associated value label. Therefore I have modified the code as per below:

putexcel set "res.xlsx", sheet("estOut")  modify
estout r3f r3r, eform cells("b ci_l ci_u p") uns label stats(r2_p N, fmt(%9.3f %9.0g) labels(Pseudo R-squared)) // <-- Puts the ORs, CIs and p vale on same row for each variable
matrix A = r(coefs)              // Save the results as a matrix
mat list A
local myrownames: rownames A       // Copy the row names excluding yvar
local startrow=7
putexcel C`startrow'=matrix(A)  // Write the matrix  in column C
foreach rowname of local myrownames {
 di _newline(3) as error "Starting rowname  =    `rowname'"
   tokenize `rowname', parse(".")
     if "`2'"!="." {               // Handle Continuous Variables
       local myrowname `1'
     }
     else {
       local myvar `3'            // Get Variable Name
       local myVarLab: variable label `myvar'      // Get Variable Label
       if "`myVarLab'"==""  {   // Handle missing variable name      
          local myVarLab `myvar'
        }
        tokenize `1', parse("b")                  // Get Value of factor
        local labelvar: value label `myvar'      // Get Value Label name
        di "Value label of `myvar'  = `labelvar'"  
        if "`labelvar'"==""  {  // Handle variables without any value label
           local myrowname `1'
           di "myrowname = `myrowname'"
         }
         else {                        
           local myrowname: label `labelvar' `1' // Get Label for factor value
           di "myrowname = `myrowname'"
         }
       putexcel A`startrow'=("`myvar'") B`startrow'=("`myrowname'")
       local startrow = `startrow' + 1
    }
}

Thank you for your useful post.
Bests
Vivek