Put anything anywhere in Excel without sweat

putexcel has recently become a very good friend. For those who (or working with people who) find comfort in working with tables in Excel after data processing or estimation in Stata (yes, there are others who don’t find comfort in this.) and already into Stata 13, learning putexcel could be very helpful (put an end to copy-pasting!). A number of user-written commands, such as outreg [1], outreg2, tabout, are also already available for similar purposes. What puts putexcel apart is its ‘user-friendliness’ and flexibility. You can put anything anywhere in Excel without sweat.

Continue reading

Generating scalars for coefficients or standard errors after regression

Besides displaying output in the results window, Stata stores results that you can use as inputs to subsequent commands. We have shown examples of using saved results in Writing Greek letters and other symbols in graphs and Ways to count the number of unique values in a variable where we used results stored in r(). In this post, we will use estimation results saved in e() after -regress- to generate a scalar (or a local macro) for coefficients and standard errors. (See note below)

sysuse auto /* opens example data auto.dta */

reg price mpg /* estimates the equation price = b0 + b1*mpg + e ; to display all saved results after -regress-, type “ereturn list” */

matrix b=e(b)
matrix V=e(V)
/* defines matrix b equal to the row vector of estimated coefficients, e(b); and  matrix V equal to the variance-covariance matrix, e(V). */

matrix list b // or matrix list e(b)
matrix list V // or matri list e(V)
/* displays b and V */

scalar c_mpg=b[1,1]
scalar se_mpg=sqrt(V[1,1])
/* defines scalar c_mpg equal to element (1,1)  of vector b; and defines scalar se_mpg equal to the square root of element (1,1) of matrix V */

scalar list /* displays c_mpg and se_mpg */

Alternatively, you may define c_mpg and se_mpg as local macros instead of scalars:

local c_mpg=b[1,1]
local se_mpg=sqrt(V[1,1])
display “c_mpg= “c_mpg' " ; se_mpg= "se_mpg’

Note: Where Stata saves the results depends on the type of command executed. Stata commands can be classified into 5 classes—r-, e-, s-, n-, and c-class commands:

r-class: general commands that do not require parameter estimation (example: -summarize-); results are stored in r()
e-class: parameter estimation commands (example: -regress-); results are stored in e()
s-class: programming commands that assist in parsing; results are stored in s()
n-class: commands that do not save other results except those that are explicitly generated (example: -generate-); no results stored
c-lass: stores system parameters and some constants (example: c(pi) returns the value of pi); values are stored in c() (try typing “creturn list”)

Writing a formatted regression output using -outreg-

I used to copy-paste regression results into Windows Excel spreadsheet, not only to do some formatting, but also to add asterisks to indicate significance, write notes, etc., until I learned about -outreg- (thanks to Utsav). -outreg-, written by J.L. Gallup, writes formatted regression output and saves them into a specified spreadsheet. The command is especially helpful when you want to compare a number of regression results, as we always do, to check for the robustness of our results. The table below shows an example of an -outreg- result.

How do you create this table?

reg y_var x_vars1, [robust]   /* results displayed in column (1) /
outreg x_vars using filename, [options] replace /
the replace option replaces the file filename */

reg y_var x_vars2, [robust]   /* results displayed in column (2) /
outreg x_vars using filename, [options] append /
the append option appends the result of the regression to filename /
reg y_var x_vars8, [robust]   /
results displayed in column (8) */
outreg x_vars using, [options] append

The options specified to create the table above are:

/*specifies the number of decimal places for the reported estimates */

/* specifies that R-squared will not be reported. I wanted to show R-squared, but not in its default position. I used the addstat() option to do this. */

/* specifies that * for significance levels are appended to regression coefficients */

/* specifies 3 * for 1%, 2 * for 5%, and 1 * for 10% significance levels */

/* specifies that standard errors wil be displayed, not t-statistics */

/* specifies that variable names will be reported, not variable labels */

/* specifies that [] are used, not () for t-statistics or standard errors. This is specifically helpful because, by default, Excel reads numbers in () as negative numbers. */

addstat(“text” , # [, “text”, # …])
/*specifies other statistics you want to add in the table. In this case, I added the following:
“Degrees of freedom”, e(df_r)
“R-squared”, e(r2)
“Adjusted R-squared”, e(r2_a)

More options are available for -outreg-, type “help outreg”. But, first, you need to install -outreg- by typing:

net install sg97_3.pkg

Note: The results shown in the table above are from some of the tests we (with Jesus Felipe and Utsav Kumar) did for the paper “Using Capabilities to Project Growth, 2010-2030.”