Re: st: Forreach loop with esttab

[Eric Booth <eric.a.booth@gmail.com>]

<>


There are several packages that will export table results to Excel. Roberto mentions -esttab- from SSC.

The tricky part (for me, at least) is adding in your “a” row on the fly -- there may be pre-packaged solutions to add this in from some other package, but I didn’t find it. 
Instead I use -esttab- and -parmest- (both from SSC) below to show you some techniques (that you’ll want to customize) to add the “a" row to your table, but the approach is similar for both examples: 
(1) I create the table(s) of interest using the user package and then (2) reimport those results into Stata, (3) add an extra row in which to put “a”, and (4) re-export the data to an MS Excel using -export-.  HTH


**********************Some examples — run these in a do-file


****EXAMPLE 1 using esttab
sysuse auto, clear

**clean up if you run this more than once:
cap rm "mytable.xls"
cap rm "mytable.txt"
global storelist ""


**using Roberto’s example here:
foreach yvar of varlist mpg price displacement {
   regress `yvar' foreign weight
   global  `yvar'1 =  _b[foreign]+_b[weight] 
   eststo `yvar'1
   global storelist = "${storelist} `yvar'1”
}

esttab ${storelist} using "mytable.xls",  nonumbers tab replace cells(b t(par)   ) label stats(  r2  p F ll df_m ) 


**add the a row to the bottom of table**
insheet using "mytable.xls", clear
	set obs `=_N+1'
	replace v1 = "a" in l
	loc i = 2
	foreach yvar of global storelist {
		replace v`i' = `"`:display ${`yvar'} %6.2f' "' in l
		loc `++i'
		}

export excel using "mytable1.xlsx", replace first(variable)
	

	
	
	
****EXAMPLE 2 using parmest

sysuse auto, clear

foreach yvar of varlist mpg price displacement {
   regress `yvar' foreign weight
   local `yvar'1 =  _b[foreign]+_b[weight] 
   estimates store `yvar'1
   local storelist = "`storelist' `yvar'1"
    parmest, label   sav(test.dta, replace)
preserve
 u test.dta, clear
 keep label estimate stderr
 rename estimate e1
 rename stderr e2
 g i = _n
 reshape long e , i(i) j(j)
 replace label = "" if j ==2
 drop i j
 set obs `=_N+1'
 replace e = ``yvar'1' in l
 replace label = "a" in l
 g n = _n
 rename e `yvar'
 sa `yvar'1, replace
restore
}


clear
set obs 10
g n = _n
sa "master.dta", replace emptyok
foreach v in `storelist' {
	merge 1:1 n using `"`v'"', nogen
	}
drop n

export excel using "mytable2.xlsx", replace first(variable)
**********************



- Eric
eric.a.booth@gmail.com





On Jan 12, 2014, at 9:24 AM, Muhammad Ramzan <ramzan243@gmail.com> wrote:

> Thanks Roberto Ferrer
> 
> How to export results in excel.
> 
> I have searched a lot , but unable to find how to export results in
> excel with estimates table command.
> 
> and how i can have a scalar belowm each cloumd of the table calculated
> from coefficient of each equation.
> 
> Scalar a=  (-1.6500+-0.0066)=-1.66 from equation one.
> Similarly from each equation .
> 
> like
> 
> -----------------------------------------------
>    Variable |   mpg       price     displa~t
> -------------+---------------------------------
>     foreign |  -1.6500    3.6e+03   -25.6127
>             |  -1.5335     5.4399    -2.0061
>      weight |  -0.0066     3.3207     0.0968
>             | -10.3402     8.3883    12.7984
>       _cons |  41.6797   -4.9e+03   -87.2355
>             |  19.2467    -3.6734    -3.3949
> -------------+---------------------------------
>           F |  69.7485    35.3497   152.8503
>          ll | -1.9e+02   -6.7e+02   -3.8e+02
>        chi2 |
>        df_m |   2.0000     2.0000     2.0000
>        df_r |  71.0000    71.0000    71.0000
>         a        -1.66      3603.32    -25.52
> 
> -----------------------------------------------
> 
> How this can be done with code.
> 
> Regards
> 
> MUHAMMAD RAMZAN
> 
> On 12 January 2014 19:56, Roberto Ferrer <refp16@gmail.com> wrote:
>> Several things:
>> 
>> 0. The user-written module ESTOUT, of which -esttab- is part, is
>> authored by Ben Jann and available in SSC. Although extremely popular,
>> the Statalist FAQ asks you to report where user-written commands come
>> from. Type -ssc describe estout- for a descrption.
>> 
>> 1. I prefer
>> 
>>    foreach yvar of varlist  mpg price displacement    {
>> 
>> over
>> 
>>    foreach yvar in mpg price displacement {
>> 
>> simply because you are using a variable list, not any list. It doesn't
>> affect you here but you might want to read -help foreach-.
>> 
>> 2.  I assume your code reads
>> 
>>    esttab `yvar'1 using example, t ...
>> 
>> and not
>> 
>>    esttab `yvar' using example, t ...
>> 
>> because you would be reporting a different error.
>> 
>> 3. You are using the -replace- option, which does exactly that:
>> replace a file previously created. You might want to use -append-,
>> instead. This gives you several _stacked_ tables.
>> 
>> 4. If you want side-by-side results, as suggested in -help estimates
>> store-, you can try -estimates table- :
>> 
>> clear all
>> set more off
>> 
>> sysuse auto
>> 
>> foreach yvar of varlist mpg price displacement {
>>    regress `yvar' foreign weight
>>    estimates store `yvar'1
>> 
>>    local storelist = "`storelist' `yvar'1"
>> }
>> 
>> estimates table `storelist', b(%7.4f) t(%7.4f) stats(F ll chi2 df_m df_r)
>> 
>> 
>> or get rid of the macro -storelist- and use the system variable _all.
>> 
>> On Sun, Jan 12, 2014 at 5:10 AM, Muhammad Ramzan <ramzan243@gmail.com> wrote:
>>> Assalam-o-Alaikum
>>> 
>>> Hi Everyone I am using the foreach lopp command with esttab command.
>>> here in the final table i should get results of three equations. but
>>> here i am only getting the results of last euquation that is, in which
>>> is dependent variable is  " displacement ".
>>> 
>>> foreach yvar in mpg price displacement {
>>> reg `yvar' foreign weight
>>> estimates store `yvar'1
>>> 
>>> esttab `yvar' using example, t scalars(F ll chi2 df_m df_r) nodepvars  replace
>>> 
>>> }
>>> 
>>> 
>>> 
>>> Regards
>>> Muhammad Ramzan
>>> *
>>> *   For searches and help try:
>>> *   http://www.stata.com/help.cgi?search
>>> *   http://www.stata.com/support/faqs/resources/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/faqs/resources/statalist-faq/
>> *   http://www.ats.ucla.edu/stat/stata/
> 
> 
> 
> -- 
> Assalam-o-Alaikum
> 
> 
> 
> 
> 
> 
> 
> Muhammad Ramzan
> Lecturer (Economics)
> Pakistan Institute of Development Economics
> Islamabad
> *
> *   For searches and help try:
> *   http://www.stata.com/help.cgi?search
> *   http://www.stata.com/support/faqs/resources/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/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/