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

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

<>

For issue #(1), changing the -bys- to if catg==`i' in my previous example seems to fix your issue.

Issue #2 is due to the limit of 300 stored estimates (see -help limits-), so for 400 results, you could break up your loop to run models 1/200 and 201/400 and then append them before you -sxpose-.
This should do the trick:

*******************!
estimates clear  
sysuse auto, clear 
expand 1000
g rcat = int((1000)*runiform())
**Create 400 groups for tables**
egen catg = cut(rcat),group(400)
tab1 catg

qui levelsof catg, local(levels)
forval i = 1/200 {
	qui regress price mpg weight foreign ///
	   if catg==`i'   // I changed this
	qui 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)

est clear
forval i = 201/399 {
	qui regress price mpg weight foreign ///
	   if catg==`i'   // I changed this
	qui eststo model`i'
	}
****
estout * using "test.txt" , cells(b(star fmt(3))) ///
stats(r2_a N depvar, fmt(%5.2f %9.0g))  ///
collabels(none)  append 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:33 PM, Benhoen2 wrote:

> I just realized 2 more issues
> 
> 1) there is an error in the code for the loop.  As you can see although the
> loop produced correctly named models (e.g., "modelO", "model11") the results
> did not change.
> 
> Previously I had used this .do file:
> 
> sysuse auto, replace
> g rcat = int((100)*runiform())
> egen catg = cut(rcat),group(4)
> tab1 catg
> eststo clear
> bys catg: eststo: qui regress price mpg weight foreign
> estout,cells(b(star fmt(3))) stats (r2_a N depvar, fmt(%5.2f %9.0g))
> collabels(none)
> 
> which produces this output:
> 
> ----------------------------------------------------------------------------
>                     est1            est2            est3            est4   
> ----------------------------------------------------------------------------
> mpg               132.515         -52.392         147.159         -14.065   
> weight              4.334**         4.211*          3.879           2.503*  
> foreign          4888.586**      3391.147        3322.140        3981.645** 
> _cons          -11453.831       -5648.428       -9647.244       -2368.237   
> ----------------------------------------------------------------------------
> r2_a                 0.61            0.48            0.35            0.44   
> N                      18              19              17              20   
> depvar              price           price           price           price   
> ----------------------------------------------------------------------------
> 
> but this does not change the model names.  Is there a way to combine the
> two?
> 
> 2) Potentially more important.  When I tried running the loop with a large
> number of groups I maxed out the number of stored results.  
> 
> "system limit exceeded
> you need to drop one or more models
> r(1000);"
> 
> Any ideas to get all of this into one set of code would be great. 
> 
> Ben 
> 
> 
> Ben Hoen
> LBNL
> Office: 845-758-1896
> Cell: 718-812-7589
> bhoen@lbl.gov
> 
> 
> -----Original Message-----
> From: Benhoen2 [mailto:benhoen2@earthlink.net] 
> Sent: Wednesday, November 03, 2010 3:05 PM
> To: 'statalist@hsphsun2.harvard.edu'
> Cc: 'Benhoen2'
> Subject: inverting a stored table of regression results - then exporting to
> a cvs/excel file
> 
> 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/