Re: st: inverting a stored table of regression results - then exporting to a cvs/excel file

[Eric Booth <ebooth@ppri.tamu.edu>]

<>

I couldn't find an option in -estout- to transpose the table, but you can always -insheet- the result and use -sxpose- (from SSC) to transpose and -outsheet- the result.  This approach preserves the "stars" and you can change the format to .csv, etc.


*******************!
estimates clear  
sysuse auto, clear 

g rcat = int((100)*runiform())
egen catg = cut(rcat),group(20)
tab1 catg

qui levelsof catg, local(levels)
qui foreach i of local levels {
	regress price mpg weight foreign ///
	   if catg==`i'   // I changed this
	eststo model`i'
	}
****
estout * using "test.txt" , cells(b(star fmt(3))) ///
stats(r2_a N depvar, fmt(%5.2f %9.0g))  ///
collabels(none)  replace style(tab)

preserve
clear
insheet using "test.txt", tab nonames
sxpose, clear
outsheet using "test_transposed.xls", replace nonames
restore



//open tables in Windows or Mac OSX//
if "`c(os)'" == "MacOSX" local i open
if "`c(os)'" == "Windows" local i start
!`i' "test_transposed.xls"
}
****************!

- Eric


__
Eric A. Booth
Public Policy Research Institute
Texas A&M University
ebooth@ppri.tamu.edu
Office: +979.845.6754
Fax: +979.845.0249
http://ppri.tamu.edu

On Nov 3, 2010, at 2:05 PM, Benhoen2 wrote:

> Hello statalisters,
> 
> I have a .do file that produces a stored set of results:
> 
> sysuse auto, replace
> g rcat = int((100)*runiform())
> egen catg = cut(rcat),group(4)
> tab1 catg
> 
> eststo clear
> qui levelsof catg, local(levels)
> qui foreach i of local levels {
> 	regress price mpg weight foreign
> 	eststo model`i'
> 	}
> *
> estout,cells(b(star fmt(3))) stats (r2_a N depvar, fmt(%5.2f %9.0g))
> collabels(none)
> 
> This produces this output:
> 
> 
> ----------------------------------------------------------------------------
>                   model0          model1          model2          model3   
> ----------------------------------------------------------------------------
> mpg                21.854          21.854          21.854          21.854   
> weight              3.465***        3.465***        3.465***        3.465***
> foreign          3673.060***     3673.060***     3673.060***     3673.060***
> _cons           -5853.696       -5853.696       -5853.696       -5853.696   
> ----------------------------------------------------------------------------
> r2_a                 0.48            0.48            0.48            0.48   
> N                      74              74              74              74   
> depvar              price           price           price           price   
> ----------------------------------------------------------------------------
> 
> I will run a similar .do file with a relatively small number of regressors
> yet over a relatively large number of groups (n=400).  Ideally I would like
> to be able to invert the output so that the regressors are across the top
> and the model numbers are rows. Is that possible?  I played around with
> "estout matrix(matname)" but was unclear how to apply this.  Any help here
> would be great.
> 
> Secondly I want to export this output to a csv or excel file for further
> work.  Again, ideally I could preserve the format, for instance the stars.
> Any ideas?  I was able to clumsily do this by copying and special pasting
> "transpose", removing rows etc. into excel, but it would be great if I could
> automate at least parts of it. 
> 
> Thanks, as always,
> 
> Ben
> Ben Hoen
> LBNL
> Office: 845-758-1896
> Cell: 718-812-7589
> bhoen@lbl.gov
> 
> 
> 
> *
> *   For searches and help try:
> *   http://www.stata.com/help.cgi?search
> *   http://www.stata.com/support/statalist/faq
> *   http://www.ats.ucla.edu/stat/stata/



*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/