Esttab Custom Confidence Intervals

[Benedikt Franz]

Hello everyone,

I am conducting a tax pass-through analysis. Therefore, I use a DiD approach with areg, log prices and a treat#post interaction term. The estimates report the price effect in percent. However, I would like to show the estimates and the CIs as pass-through rates essentially recreating the table shown below.

I manage to accomplish this for the estimates, but I struggle finding a way to do the same for the corresponding CIs.

My code is as follows:

eststo e5: quietly areg ln_e5 i.date 1.treat#1.post, cluster(id) a(id)
estadd scalar pt = _b[1.treat#1.post]/(-0.03/1.19)*100
estadd scalar lower_ci = (_b[1.treat#1.post] - invttail(e(df_r),0.025)*_se[1.treat#1.post])//(-0.03/1.19)*100
estadd scalar upper_ci = (_b[1.treat#1.post] + invttail(e(df_r),0.025)*_se[1.treat#1.post])//(-0.03/1.19)*

* similarly for E10 and Diesel

esttab using "output.tex", ///
keep(_cons 1.treat#1.post) star(* 0.10 ** 0.05 *** 0.01) cells(b(star fmt(%9.6fc)) se(par) ci(par)) nonumbers brackets ///
stats(pt N r2,labels("Pass-Through (in \%)" "Observations" "R-squared") fmt(%9.2fc %9.0fc %9.4fc)) ///
mtitles("E5" "E10" "Diesel" ///
label booktabs replace nogap collabels(none) nonotes

Does anyone know how to include the CIs as shown in the picture? How do I add the "%" in the cells?

I tried to formulate my problem as clear as possible. Please let me know, if things remained unclear.

Thank you very much in advance.

[Andrew Musau]

I think I could manage to do that, but provide a reproducible example first. estout is from SSC (FAQ Advice #12).

[Benedikt Franz]

Hello Andrew,

thank you so much for your quick response. You are totally right. I should have included the missing information as well as a data example. Sorry for that.

Since my time horizon is quite long with many IDs I am not sure, if the following data export is sufficient. Please let me know, how I can improve this, if it is not sufficient yet.

* Example generated by -dataex-. For more info, type help dataex
clear
input double(id date) float(ln_e5 ln_e10 ln_diesel post treat)
  205 22119         .          .    .05960537 1 1
  489 22054 .15601945    .130018   .032145374 0 1
  536 22091 .23070134   .2059456    .09840892 0 1
  566 22154 .20079905  .16734274 -.0022892323 1 1
  603 22154  .2245367   .1944976   .022109983 1 1
  864 22052 .19002077   .1648996    .02883217 0 1
  986 22124  .2489259  .21724375    .08529137 1 1
 1297 22051 .13275224  .10613054   .007342981 0 1
 1313 22157  .2292643  .19694284    .04628444 1 1
 1349 22096  .1914888          .     .0264226 0 1
 1369 22110 .25006044   .2184148     .0754935 1 1
 1544 22101  .1994778  .16616264    .02866092 1 1
 1558 22144  .2224092  .19809845    .05393391 1 1
 1821 22109  .2291387   .1968131   .032092124 1 1
 1822 22095  .4047982          .     .3213586 0 1
 1894 22046 .11724196  .09019847  .0016497283 0 1
 1899 22069         .          .    .04783735 0 1
 1916 22083         .          .     .0285875 0 1
 2010 22079  .2070521  .18236047    .03648561 0 1
 2072 22044   .189123    .163979    .04960356 0 1
 2147 22098  .2232721  .19075316   .035922576 1 1
 2155 22039 .11213258  .08494866 -.0012571904 0 1
 2416 22116  .1919273  .15835536   .021352865 1 1
 2850 22119    .28243   .2518079    .12083331 1 1
 2942 22087 .26268685  .23144452    .09917838 0 1
 3074 22061 .18555807  .16032314    .03037463 0 1
 3247 22155 .23213813   .1999109     .0544934 1 1
 3255 22090 .27325013  .25015852    .08380402 0 1
 3261 22135 .22597687   .1999109    .05358245 1 1
 3393 22147  .2034634    .170283    .04224665 1 1
 3464 22130   .233629   .2014506    .07932647 1 1
 3645 22089 .23105937  .20696054    .07343428 0 1
 3684 22150 .25676644   .2253356    .07751245 1 1
 3934 22106  .2196194   .1869795    .04409403 1 1
 3950 22119   .305984  .27608556    .07697206 1 1
 4117 22147  .2062008          .    .04783735 1 1
 4236 22079 .24454334   .2207711    .08422077 0 1
 4386 22149 .25580966  .21946314    .05351735 1 1
 4391 22061 .27692595          .    .11587887 0 1
 4442 22075 .27046955  .24731284     .0963985 0 1
 4465 22076  .2166098   .1921559    .12644948 0 1
 4651 22087 .20046186          .   .021729283 0 1
 4672 22044  .1306812  .10400354    .01919916 0 1
 4673 22055  .1650161  .13925062   .003198178 0 1
 4807 22051  .1584374  .13249959   .012235302 0 1
 5190 22099 .23970743  .20772713   .025072677 1 1
 5388 22142  .1967172   .1633084    .02432674 1 1
 5394 22083  .2914921  .26882267    .11241698 0 1
 5782 22132 .20114173   .1678829    .02631105 1 1
 5887 22094 .27507582   .2442242     .0757949 0 1
 5961 22158 .24602874   .2142532    .08381957 1 1
 6093 22038 .09845457 .070891075   .001238263 0 1
 6101 22083 .20381732   .1790447    .05481855 0 1
 6116 22114  .2223432  .19803083    .05732508 1 1
 6121 22043  .1428056   .1164537   -.00652953 0 1
 6174 22134  .2379591   .2059219    .06781806 1 1
 6385 22120 .28097904  .25031182     .1001834 1 1
 6399 22108 .24448597   .2126606    .06250436 1 1
 6461 22053 .12604187   .0992385  .0026092576 0 1
 6479 22104  .2725245  .24159282    .07797718 1 1
 6609 22154  .2233087  .19124302   .019437134 1 1
 6649 22064  .1748325  .14932196    .02044258 0 1
 6674 22042  .1656429  .14035541 -.0007897032 0 1
 6834 22135  .2299849   .1976871    .06242263 1 1
 7043 22145 .19958843   .1653771     .0179856 1 1
 7265 22110         .          .    .09251156 1 1
 7361 22137   .252283   .2207087     .0759392 1 1
 7425 22065  .4147733  .39475965     .3095853 0 1
 7834 22100 .26597884   .2348408    .10393132 1 1
 7926 22064 .15977886  .13387634   .003307731 0 1
 7942 22143 .24661896  .21486245    .07831298 1 1
 8231 22148 .26146784   .2301868     .1000094 1 1
 8573 22108  .2322361    .208166    .05960537 1 1
 8674 22098 .22389273  .20842253    .05455193 1 1
 8695 22093 .24175173  .20983782   .065148324 0 1
 8836 22051 .15313508  .12705755   .010727528 0 1
 9285 22117 .19803083  .16466664    .03825875 1 1
 9485 22102  .2914673  .26112497    .10704117 1 1
 9512 22150  .4376096   .4114471     .3213586 1 1
 9640 22037 .09892334  .07137295   .014100467 0 1
 9720 22135  .2340784  .20191465    .07098064 1 1
 9808 22147 .22324464   .1907248     .0761238 1 1
 9839 22095 .21430457  .19803083    .06672364 0 1
 9969 22042 .14880127  .12260897    .01493193 0 1
10065 22111  .2062027  .17311455     .0522753 1 1
10147 22123 .24042414  .20846713      .078404 1 1
10265 22044 .18478037  .15952556    .09062807 0 1
10504 22069 .13375017          .   -.03896075 0 1
10573 22082  .2157337   .1994834    .05948889 0 1
10664 22080 .24596037  .22222224    .07451436 0 1
10702 22059 .10911471  .09101944  -.025121063 0 1
10808 22049 .13498063    .108419  -.015747903 0 1
11036 22090 .21430457          .    .05732508 0 1
11050 22092  .2595977    .228839    .09917838 0 1
11262 22048  .1671103  .14161402  -.006825667 0 1
11347 22048  .4196576   .3997424     .3055186 0 1
11378 22113 .23571293   .2036026    .05264677 1 1
11495 22057 .16684103  .14112313   .005841902 0 1
11641 22055    .14918  .12299772  -.015389808 0 1
11738 22146 .32990175   .3007203    .17457876 1 1
end
format %tdDD_Mon_CCYY date
label values post postl
label def postl 0 "Before Reform", modify
label def postl 1 "Post Reform", modify
label values treat treatl
label def treatl 1 "Treatment Group (Germany)", modify

Thanks again!

[Andrew Musau]

I cannot get your example to run. You are responsible for ensuring that your problem is reproducible. Here, there is need for an elaborate example:

sysuse auto, clear
regress mpg weight disp turn, nocons
*CUSTOM CIs
mat ll= (55, 65, 75)
mat colnames ll= "`:colnames e(b)'"
mat ul= (57.5, 67.5, 77.5)
mat colnames ul= "`:colnames e(b)'"
*CREATE REDUNDANT MATRIX WITH UNIQUE ENTRIES
mat end= (1, 1, 1)
mat colnames end= "`:colnames e(b)'"
*ESTADD MATRICES
estadd matrix myll= ll
estadd matrix myul= ul
estadd matrix end= end
*ESTTAB WITH CUSTOM CIs
esttab ., cells(b myll(par("["))&myul&end(par("]"))  ) incelldelimiter("%, ") substitute (", ]1" "]")

Res.:

. esttab ., cells(b myll(par("["))&myul&end(par("]"))  ) incelldelimiter("%, ") substitute (", ]1" "]")

-------------------------
                      (1)
                      mpg
             b/myll/myu~d
-------------------------
weight          -.0088695
             [55%, 57.5%]
displacement    -.0188085
             [65%, 67.5%]
turn             1.298961
             [75%, 77.5%]
-------------------------
N                      74
-------------------------

[Benedikt Franz]

Hello Andrew,

thank you very much for your insightful answer. I am very sorry, that I could not give a working example.

Following up on your example I was wondering, if it is also possible to only translate the coefficient of weight in percentages with the CI in percentages below? The remaining variables would simply stay in their default setting, i.e. just coefficient. In my current setup I managed to translate the coefficients of one variable into percentages by including it in starts as shown in #1. I tried to use your comment to achieve my desired setup, but I was unable to make it work.

Do you have an idea on how I could address this problem? I hope, it is not too much to ask.

Thank you very much for your patience and help.

[Jared Greathouse]

Nothing is (usually) too much to ask on condition that you give the dataset and code you've used. If you're working with the auto data, cool, but show, in

Code

delimiters, the exact code you used to reproduce the problem. As FAQ says, saying "x didn't work" is typically never a good way of reporting a problem.

I haven't tested any of this code since it's 5am and I just woke up, but if your adaptation of Andrew's code didn't do the thing you wanted, post back the code you used so we can evaluate what the issue might have been.

[Andrew Musau]

I think I can follow #5, but as Jared Greathouse notes, showing what did not work is the best way to illustrate an issue. You can create a similar Table to #1 by appending results, specifying different options for each set of results. Below, you will need to delete one line ("\hline" in LaTeX), but you can explore the -plain- option of estout which will allow you to define the horizontal lines yourself. I have at least one post where I show how you can do this if you search the forum.

sysuse auto, clear
regress mpg weight disp turn, nocons
*CUSTOM CIs
mat ll= (55, 65, 75)
mat colnames ll= "`:colnames e(b)'"
mat ul= (57.5, 67.5, 77.5)
mat colnames ul= "`:colnames e(b)'"
*CREATE REDUNDANT MATRIX WITH UNIQUE ENTRIES
mat end= (1, 1, 1)
mat colnames end= "`:colnames e(b)'"
*ESTADD MATRICES
estadd matrix myll= ll
estadd matrix myul= ul
estadd matrix end= end
estimates store m1
*ESTTAB WITH CUSTOM CIs
esttab m1 using myfile.rtf, label cells(b(fmt(3)) ///
myll(par("[") fmt(a3))&myul(fmt(a3))&end(par("]") fmt(0))  ) ///
incelldelimiter("%, ") substitute (", ]1" "]") keep(weight) noobs ///
replace mlab("`e(depvar)'") collab(none) modelwidth(20)
*APPEND OTHER RESULTS, SPECIFYING DIFFERENT OPTIONS
esttab m1 using myfile.rtf, label se drop(weight) b(3) append nonumber mlab(none) modelwidth(20)

Res.:

[Benedikt Franz]

Hello Jared,

thank you for this comment. Here a reproducible example for my problem using the auto data:

sysuse auto, clear
eststo weight: regress mpg weight disp turn, nocons

* Coefficient of weight in percentages
estadd scalar coef = _b[weight]/(-0.01)*100

* CI of weight in percentages
estadd scalar lower_ci = (_b[weight] - invttail(e(df_r),0.025)*_se[weight])/(-0.01)*100
estadd scalar upper_ci = (_b[weight] + invttail(e(df_r),0.025)*_se[weight])/(-0.01)*100

* Esttab with coefficient and CI in percentages
esttab ., cells(b se(par)) stats(coef lower_ci upper_ci N)

Results

. esttab ., cells(b se(par)) stats(coef lower_ci upper_ci N)

-------------------------
                      (1)
                      mpg
                     b/se
-------------------------
weight          -.0088695
               (.0020049)
displacement    -.0188085
               (.0136858)
turn             1.298961
               (.1053446)
-------------------------
coef             88.69475
lower_ci         128.6712
upper_ci         48.71824
N                      74
-------------------------

In this example, dividing the coefficient and CI by -0.01 is arbitrary. However, for my pass-through analysis, I will replace this value with the hypothetical full pass-through.

The example shows my desired outcome: Showing all coefficients in their default nature and showing the coefficient and CI for one variable (weight) in percentages (of the full pass-through). Adding the transformed coefficient and CI as a scalar and showing it in the stats section, is the only way, that has worked for me so far.

My main question: Is it possible to achieve the following (round to two digits, add %, and show CI side-by-side)?

-------------------------
coef             88.69%
ci         [128.67%, 48.72%]
N                      74
-------------------------

If it is not "too much trouble": Is there a way, in which I could display the coefficient with weight as a "new variable" below weight?

-------------------------
                      (1)
                      mpg
                     b/se
-------------------------
weight          -.0088695
               (.0020049)
pass-through     88.69%
                    [128.67%, 48.72%]
displacement    -.0188085
               (.0136858)
turn             1.298961
               (.1053446)
-------------------------
N                      74
-------------------------

I really hope, that this expresses my problem well and gives a reproducible example.

Thank you very much!

[Benedikt Franz]

Thank you for your help Andrew Musau. When you posted your answer, I was working on a better representation of my problem (#8). I hope, things are clearer now.

[Andrew Musau]

As I showed in #7, one way to directly replace coefficients and SEs is to use the -substitute()- option. You can store these in locals. Another option is to use the -transform()- option, although including the percentage signs will require further manipulations. Finally, you have the solution in #7 which stores the transformed coefficients and CIs in separate matrices, but this will require appending of results.

sysuse auto, clear
eststo weight: regress mpg weight disp turn, nocons

* Coefficient of weight in percentages
local coef = _b[weight]/(-0.01)*100
local coefweight= _b[weight]
local seweight=_se[weight]

* CI of weight in percentages
local lower_ci = (_b[weight] - invttail(e(df_r),0.025)*_se[weight])/(-0.01)*100
local upper_ci = (_b[weight] + invttail(e(df_r),0.025)*_se[weight])/(-0.01)*100

* Esttab with coefficient and CI in percentages
esttab ., cells(b(fmt(3)) se(par fmt(3))) ///
substitute("`:di %9.3f `coefweight''" "`:di %9.2f `coef''%" ///
"(0`=string(`:di %9.3f `seweight'')')" ///
"[`:di %3.2f `lower_ci''%, `:di %3.2f `upper_ci''%]")

Res.:

. * Esttab with coefficient and CI in percentages
. esttab ., cells(b(fmt(3)) se(par fmt(3))) ///
> substitute("`:di %9.3f `coefweight''" "`:di %9.2f `coef''%" ///
> "(0`=string(`:di %9.3f `seweight'')')" ///
> "[`:di %3.2f `lower_ci''%, `:di %3.2f `upper_ci''%]")

-------------------------
                      (1)
                      mpg
                     b/se
-------------------------
weight              88.69%
                  [128.67%, 48.72%]
displacement       -0.019
                  (0.014)
turn                1.299
                  (0.105)
-------------------------
N                      74
-------------------------

[Andrew Musau]

In -rtf- format, using the -plain- option for the second set of estimates gets you very close to the output in #1. If you export as a -tex- file, you will need to merge the tables into one by copying and pasting.

sysuse auto, clear
regress mpg weight disp turn, nocons
*CUSTOM CIs
mat ll= (55, 65, 75)
mat colnames ll= "`:colnames e(b)'"
mat ul= (57.5, 67.5, 77.5)
mat colnames ul= "`:colnames e(b)'"
*CREATE REDUNDANT MATRIX WITH UNIQUE ENTRIES
mat end= (1, 1, 1)
mat colnames end= "`:colnames e(b)'"
*ESTADD MATRICES
estadd matrix myll= ll
estadd matrix myul= ul
estadd matrix end= end
estimates store m1
*ESTTAB WITH CUSTOM CIs
esttab m1 using myfile.rtf, label cells(b(fmt(3)) ///
myll(par("[") fmt(a3))&myul(fmt(a3))&end(par("]") fmt(0))  ) ///
incelldelimiter("%, ") substitute (", ]1" "]") keep(weight) noobs ///
replace mlab("`:var lab `e(depvar)''") collab(none) modelwidth(20)
*APPEND OTHER RESULTS, SPECIFYING DIFFERENT OPTIONS
esttab m1 using myfile.rtf, plain label ///
cells(b(fmt(3)) se(par("(") fmt(3))&end(par(")") fmt(0))) ///
drop(weight) append nonumber collab(none) mlab(none) ///
noobs modelwidth(20) substitute(" )1" ")")
*ADD STATISTICS
esttab m1 using myfile.rtf, append label drop(*) ///
mlab("") collab(none) nonum modelwidth(20)

[Benedikt Franz]

Thank you so much, Andrew Musau, for your help and your patience. I really appreciate it. You have helped me a lot!