Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: st: Followup: inverting a stored table of regression results - then exporting to a cvs/excel file
From
Eric Booth <[email protected]>
To
"<[email protected]>" <[email protected]>
Subject
Re: st: Followup: inverting a stored table of regression results - then exporting to a cvs/excel file
Date
Wed, 3 Nov 2010 19:45:48 +0000
<>
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
[email protected]
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
> [email protected]
>
>
> -----Original Message-----
> From: Benhoen2 [mailto:[email protected]]
> Sent: Wednesday, November 03, 2010 3:05 PM
> To: '[email protected]'
> 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
> [email protected]
>
>
>
> *
> * 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/