Flipping models, coefficients, number of observations and number of clusters with esttab

[Matt Shorter]

Hello everyone,

Firstly, I am a Stata 14 user. I have a question regarding esttab command and flipping models. My question might be considered as an extension of the question below,

https://www.statalist.org/forums/for...-nnmatch-coefs

I use three different specifications in the form of xtreg and xtivreg2 (i am using two different instruments). There are approximately 30 independent variables that I ran xtreg and xtivreg2. Hence, I have three column of results for a given independent variable. I want to place dependent variables in rows. When I use the code below, which I got from the above post, I can successfully get the coefficients and standard errors for my estimations in the format that I want to have. However, I want my table to include number of observation and number of cluster columns as well. Hence, I need a column of number of observations after the OLS (xtreg) column. Similarly, I need a column of number of observations and a column of number of clusters for the two xtivreg2 columns. My priority is to get a number of observations column after three different specification.

preserve 
eststo clear
eststo Subsidiesandothertransfers: xtreg Subsidiesandothertransfers distinctparty  if pr==1, fe cluster(idn)
eststo gtr_centaxdir: xtreg gtr_centaxdir distinctparty  if pr==1, fe cluster(idn)
esttab, noconstant se nostar r2
matrix C = r(coefs)
matrix S = r(stats)
eststo clear
local rnames2 : rownames C
local rnames
foreach name of local rnames2 {
    local rnames `rnames' `=strtoname("`name'")'
}
cap mat rownames C= "`rnames'"
local models : coleq C
local models : list uniq models
local i 0
foreach name of local rnames {
    local ++i
    local j 0
    capture matrix drop b
    capture matrix drop se
    foreach model of local models {
        local ++j
        matrix tmp = C[`i', 2*`j'-1]
        if tmp[1,1]<. {
            matrix colnames tmp = `model'
            matrix b = nullmat(b), tmp
            matrix tmp[1,1] = C[`i', 2*`j']
            matrix se = nullmat(se), tmp
        }
    }
    ereturn post b
    quietly estadd matrix se
    eststo `name'
}
local rnames2 : rownames S
local rnames
foreach name of local rnames2 {
    local rnames `rnames' `=strtoname("`name'")'
}
cap mat rownames S= "`rnames'"

local i 0
foreach name of local snames {
    local ++i
    local j 0
    capture matrix drop b
    foreach model of local models {
        local ++j
        matrix tmp = S[`i', `j']
        matrix colnames tmp = `model'
        matrix b = nullmat(b), tmp
    }
    ereturn post b
    eststo `name'
}
esttab, se mtitle noobs compress nonumb
est save res1, replace
restore, preserve
eststo clear

eststo Subsidiesandothertransfers: xtivreg2 Subsidiesandothertransfers (distinctparty =  gol_adm)  if pr==1, fe cluster(idn)
eststo gtr_centaxdir: xtivreg2 gtr_centaxdir (distinctparty =  gol_adm)  if pr==1, fe cluster(idn)
esttab, se nostar r2
matrix C = r(coefs)
matrix S = r(stats)
eststo clear
local rnames2 : rownames C
local rnames
foreach name of local rnames2 {
    local rnames `rnames' `=strtoname("`name'")'
}
cap mat rownames C= "`rnames'"
local models : coleq C
local models : list uniq models
local i 0
foreach name of local rnames {
    local ++i
    local j 0
    capture matrix drop b
    capture matrix drop se
    foreach model of local models {
        local ++j
        matrix tmp = C[`i', 2*`j'-1]
        if tmp[1,1]<. {
            matrix colnames tmp = `model'
            matrix b = nullmat(b), tmp
            matrix tmp[1,1] = C[`i', 2*`j']
            matrix se = nullmat(se), tmp
        }
    }
    ereturn post b
    quietly estadd matrix se
    eststo `name'
}
local rnames2 : rownames S
local rnames
foreach name of local rnames2 {
    local rnames `rnames' `=strtoname("`name'")'
}
cap mat rownames S= "`rnames'"

local i 0
foreach name of local snames {
    local ++i
    local j 0
    capture matrix drop b
    foreach model of local models {
        local ++j
        matrix tmp = S[`i', `j']
        matrix colnames tmp = `model'
        matrix b = nullmat(b), tmp
    }
    ereturn post b
    eststo `name'
}
esttab, se mtitle noobs compress nonumb
estimates save res2, replace
eststo clear

restore, preserve
eststo clear
eststo Subsidiesandothertransfers: xtivreg2 Subsidiesandothertransfers (distinctparty =  seatlvoter )  if pr==1, fe cluster(idn)
eststo gtr_centaxdir: xtivreg2 gtr_centaxdir (distinctparty =  seatlvoter )  if pr==1, fe cluster(idn)
esttab, se nostar r2
matrix C = r(coefs)
matrix S = r(stats)
eststo clear
local rnames2 : rownames C
local rnames
foreach name of local rnames2 {
    local rnames `rnames' `=strtoname("`name'")'
}
cap mat rownames C= "`rnames'"
local models : coleq C
local models : list uniq models
local i 0
foreach name of local rnames {
    local ++i
    local j 0
    capture matrix drop b
    capture matrix drop se
    foreach model of local models {
        local ++j
        matrix tmp = C[`i', 2*`j'-1]
        if tmp[1,1]<. {
            matrix colnames tmp = `model'
            matrix b = nullmat(b), tmp
            matrix tmp[1,1] = C[`i', 2*`j']
            matrix se = nullmat(se), tmp
        }
    }
    ereturn post b
    quietly estadd matrix se
    eststo `name'
}
local rnames2 : rownames S
local rnames
foreach name of local rnames2 {
    local rnames `rnames' `=strtoname("`name'")'
}
cap mat rownames S= "`rnames'"

local i 0
foreach name of local snames {
    local ++i
    local j 0
    capture matrix drop b
    foreach model of local models {
        local ++j
        matrix tmp = S[`i', `j']
        matrix colnames tmp = `model'
        matrix b = nullmat(b), tmp
    }
    ereturn post b
    eststo `name'
}

esttab, se mtitle noobs compress nonumb
est save res3, replace

estimates use res1
eststo res1
estimates use res2
eststo res2
estimates use res3
eststo res3
esttab res1 res2 res3, mtitles("OLS" "IV1" "IV2" ) nonumb noobs label

Thanks in advance.

Sincerely,
Matt

[Andrew Musau]

Save these in a local at the point you are running the regressions. You can call N corresponding to res1 "N1", corresponding to res2 "N2", and so on. These are available from ereturn list after running the commands, e.g., N is stored as e(N). Same with clusters. Then use estimates restore + estadd scalar.

*THESE SHOULD BE SAVED WHILE RUNNING REGRESSIONS
local N1= 300
local N2= 350
local N3=400
local clust1 = 30
local clust2= 35
local clust3=40

*AT THE END
forval i=1/3{
    est restore res`i'
    estadd scalar N= `N`i''
    estadd scalar clust = `clust`i''
} 
esttab res1 res2 res3, mtitles("OLS" "IV1" "IV2" ) scalars(N clust) nonumb noobs label

Res.:

. esttab res1 res2 res3, mtitles("OLS" "IV1" "IV2" ) scalars(N clust) nonumb noobs label

--------------------------------------------------------------------
                              OLS             IV1             IV2   
--------------------------------------------------------------------
Subsidies and..XPN~]        0.918**         11.91*          3.181***
                           (3.05)          (2.37)          (5.09)   

Share Direct Taxes          0.628*         -2.461          -1.269   
                           (2.18)         (-0.98)         (-0.41)   
--------------------------------------------------------------------
N                             320             350             400   
clust                          30              35              40   
--------------------------------------------------------------------
t statistics in parentheses
* p<0.05, ** p<0.01, *** p<0.001

[Matt Shorter]

Thank you very much Andrew, I was hoping that you would answer my question. Probably what I am asking is not possible or very complicated but I want to be sure about it. Since I would use more than 30 variables like Subsidies and Share Direct Taxes as the row variables, the observation number for each coefficient in the OLS column (same for other IV1 and IV2) would be different. Can I somehow add a fourth column to this table as the number of observations? Because if I can do that it would not be difficult to specify to have same observation number for OLS, IV1 and IV2 for the given dependent variable. Thank you for your answer again, even if what i am asking is not possible, your solution would help me tremendously.

Sincerely,
Matt

[Andrew Musau]

I think I can work out something, but I am confused about your request. If we add a fourth column, does that mean that the number of observations in the OLS and two IV regressions are the same? I would expect that they differ. What about a fifth column with the number of clusters? Or do you just choose to report statistics corresponding to OLS?

[Matt Shorter]

You are totally right to confuse since I could not explain it well. I thought that I can equalize the observation numbers of OLS and IV1 by adding OLS regression a specification such that (gol_adm is my first instrument)

Subsidiesandothertransfers: xtreg Subsidiesandothertransfers distinctparty if pr==1 & gol_adm!=., fe cluster(idn)

So that I can have a table of OLS and IV1 that both have the same number of observations which can be a third column. If a fourth column for the number of clusters of IV1 can be added, it would be great.

Then, I can do the same thing for OLS and IV2. In the end, I would have two different tables for two different instruments. Moreover, I will do all those regression for three different specifications (for different electoral systems), so probably the table would be very big. Hence, it might be better to represent (OLS, IV1) and (OLS, IV2) tables seperately. Otherwise, as you pointed out, I need to have different number of observations columns after each OLS, IV1 and IV2 which would be problematic. Many thanks.

Sincerely,
Matt

[Andrew Musau]

You will need to run the OLS regressions to pick up the number of observations and number of clusters. I slightly modify Ben Jann's unpublished program appendmodels to append these statistics across the models. The modification to your do file is as follows:

preserve 
eststo clear
eststo Subsidiesandothertransfers: xtreg Subsidiesandothertransfers distinctparty  if pr==1, fe cluster(idn)
eststo gtr_centaxdir: xtreg gtr_centaxdir distinctparty  if pr==1, fe cluster(idn)
esttab, noconstant se nostar r2
matrix C = r(coefs)
matrix S = r(stats)
eststo clear
local rnames2 : rownames C
local rnames
foreach name of local rnames2 {
    local rnames `rnames' `=strtoname("`name'")'
}
cap mat rownames C= "`rnames'"
local models : coleq C
local models : list uniq models
local i 0
foreach name of local rnames {
    local ++i
    local j 0
    capture matrix drop b
    capture matrix drop se
    foreach model of local models {
        local ++j
        matrix tmp = C[`i', 2*`j'-1]
        if tmp[1,1]<. {
            matrix colnames tmp = `model'
            matrix b = nullmat(b), tmp
            matrix tmp[1,1] = C[`i', 2*`j']
            matrix se = nullmat(se), tmp
        }
    }
    ereturn post b
    quietly estadd matrix se
    eststo `name'
}
local rnames2 : rownames S
local rnames
foreach name of local rnames2 {
    local rnames `rnames' `=strtoname("`name'")'
}
cap mat rownames S= "`rnames'"

local i 0
foreach name of local snames {
    local ++i
    local j 0
    capture matrix drop b
    foreach model of local models {
        local ++j
        matrix tmp = S[`i', `j']
        matrix colnames tmp = `model'
        matrix b = nullmat(b), tmp
    }
    ereturn post b
    eststo `name'
}
esttab, se mtitle noobs compress nonumb
est save res1, replace
restore, preserve
eststo clear

eststo Subsidiesandothertransfers: xtivreg2 Subsidiesandothertransfers (distinctparty =  gol_adm)  if pr==1, fe cluster(idn)
eststo gtr_centaxdir: xtivreg2 gtr_centaxdir (distinctparty =  gol_adm)  if pr==1, fe cluster(idn)
esttab, se nostar r2
matrix C = r(coefs)
matrix S = r(stats)
eststo clear
local rnames2 : rownames C
local rnames
foreach name of local rnames2 {
    local rnames `rnames' `=strtoname("`name'")'
}
cap mat rownames C= "`rnames'"
local models : coleq C
local models : list uniq models
local i 0
foreach name of local rnames {
    local ++i
    local j 0
    capture matrix drop b
    capture matrix drop se
    foreach model of local models {
        local ++j
        matrix tmp = C[`i', 2*`j'-1]
        if tmp[1,1]<. {
            matrix colnames tmp = `model'
            matrix b = nullmat(b), tmp
            matrix tmp[1,1] = C[`i', 2*`j']
            matrix se = nullmat(se), tmp
        }
    }
    ereturn post b
    quietly estadd matrix se
    eststo `name'
}
local rnames2 : rownames S
local rnames
foreach name of local rnames2 {
    local rnames `rnames' `=strtoname("`name'")'
}
cap mat rownames S= "`rnames'"

local i 0
foreach name of local snames {
    local ++i
    local j 0
    capture matrix drop b
    foreach model of local models {
        local ++j
        matrix tmp = S[`i', `j']
        matrix colnames tmp = `model'
        matrix b = nullmat(b), tmp
    }
    ereturn post b
    eststo `name'
}
esttab, se mtitle noobs compress nonumb
estimates save res2, replace
eststo clear

restore, preserve
eststo clear
eststo Subsidiesandothertransfers: xtivreg2 Subsidiesandothertransfers (distinctparty =  seatlvoter )  if pr==1, fe cluster(idn)
eststo gtr_centaxdir: xtivreg2 gtr_centaxdir (distinctparty =  seatlvoter )  if pr==1, fe cluster(idn)
esttab, se nostar r2
matrix C = r(coefs)
matrix S = r(stats)
eststo clear
local rnames2 : rownames C
local rnames
foreach name of local rnames2 {
    local rnames `rnames' `=strtoname("`name'")'
}
cap mat rownames C= "`rnames'"
local models : coleq C
local models : list uniq models
local i 0
foreach name of local rnames {
    local ++i
    local j 0
    capture matrix drop b
    capture matrix drop se
    foreach model of local models {
        local ++j
        matrix tmp = C[`i', 2*`j'-1]
        if tmp[1,1]<. {
            matrix colnames tmp = `model'
            matrix b = nullmat(b), tmp
            matrix tmp[1,1] = C[`i', 2*`j']
            matrix se = nullmat(se), tmp
        }
    }
    ereturn post b
    quietly estadd matrix se
    eststo `name'
}
local rnames2 : rownames S
local rnames
foreach name of local rnames2 {
    local rnames `rnames' `=strtoname("`name'")'
}
cap mat rownames S= "`rnames'"

local i 0
foreach name of local snames {
    local ++i
    local j 0
    capture matrix drop b
    foreach model of local models {
        local ++j
        matrix tmp = S[`i', `j']
        matrix colnames tmp = `model'
        matrix b = nullmat(b), tmp
    }
    ereturn post b
    eststo `name'
}

esttab, se mtitle noobs compress nonumb
est save res3, replace

eststo clear
xtreg Subsidiesandothertransfers distinctparty  if pr==1, fe cluster(idn)
local depvar "`e(depvar)'"
rename `depvar' holding
gen `depvar'= e(N) in 1
eststo N1: mean `depvar'
drop `depvar'
rename holding `depvar'

xtreg gtr_centaxdir distinctparty  if pr==1, fe cluster(idn)
local depvar "`e(depvar)'"
rename `depvar' holding
gen `depvar'= e(N) in 1
eststo N2: mean `depvar'
drop `depvar'
rename holding `depvar'


*PROGRAM TO APPEND COEFFICIENTS
capt prog drop appendonlyb
*Modification of appendmodels ! version 1.0.0  14aug2007  Ben Jann
program appendonlyb, eclass
    // using first equation of model
    version 8
    syntax namelist
    tempname b V tmp
    foreach name of local namelist {
        qui est restore `name'
        mat `tmp' = e(b)
        local eq1: coleq `tmp'
        gettoken eq1 : eq1
        mat `tmp' = `tmp'[1,"`eq1':"]
        local cons = colnumb(`tmp',"_cons")
        if `cons'<. & `cons'>1 {
            mat `tmp' = `tmp'[1,1..`cons'-1]
        }
        mat `b' = nullmat(`b') , `tmp'
        mat `tmp' = e(V)
        mat `tmp' = `tmp'["`eq1':","`eq1':"]
        if `cons'<. & `cons'>1 {
            mat `tmp' = `tmp'[1..`cons'-1,1..`cons'-1]
        }
        capt confirm matrix `V'
        if _rc {
            mat `V' = `tmp'
        }
        else {
            mat `V' = ///
            ( `V' , J(rowsof(`V'),colsof(`tmp'),0) ) \ ///
            ( J(rowsof(`tmp'),colsof(`V'),0) , `tmp' )
        }
    }
    local names: colfullnames `b'
    mat coln `V' = `names'
    mat rown `V' = `names'
    eret post `b' 
    eret local cmd "whatever"
end

eststo N: appendonlyb N1 N2


xtreg Subsidiesandothertransfers distinctparty  if pr==1, fe cluster(idn)
local depvar "`e(depvar)'"
rename `depvar' holding
gen `depvar'= e(N_clust) in 1
eststo clust1: mean `depvar'
drop `depvar'
rename holding `depvar'

xtreg gtr_centaxdir distinctparty  if pr==1, fe cluster(idn)
local depvar "`e(depvar)'"
rename `depvar' holding
gen `depvar'= e(N_clust) in 1
eststo clust2: mean `depvar'
drop `depvar'
rename holding `depvar'

eststo clust: appendonlyb clust1 clust2

foreach est in res1 res2{
    estimates use `est'
    eststo `est'
}

esttab res1 res2 N clust, mtitles("OLS" "IV1" "N" "Clusters" ) keep(Subsidiesandothertransfers gtr_centaxdir) nonumb noobs label

Res.:

. esttab res1 res2 N clust, mtitles("OLS" "IV1" "N" "Clusters" ) keep(Subsidiesandothertransfers gtr_centaxdir) n
> onumb noobs label

------------------------------------------------------------------------------------
                              OLS             IV1               N        Clusters   
------------------------------------------------------------------------------------
Subsidies and..XPN~]        0.918**         11.91*            327              64   
                           (3.05)          (2.37)                                   

Share Direct Taxes          0.628*         -2.461             275              22   
                           (2.18)         (-0.98)                                   
------------------------------------------------------------------------------------
t statistics in parentheses
* p<0.05, ** p<0.01, *** p<0.001

.

[Matt Shorter]

It is perfectly working, thank you a lot, you've made my day again. I have one question left because as you solve my problems I understand that I made false assumptions. In this case, I thought that I could add two more OLS, IV1, N and Clusters column to the same table. Now as I tried to do that it seems quite complicated. I added a picture of what I have on my mind, it is a bad draw though. I did those regressions for pr=1 specification, in the same table I need to have 2 more specifications in the form of pr!=1 and all (pr==1&pr!=1). Is it possible to have such a table in Stata? If it is not possible or difficult to do, I can happily go with your previous table which is perfect. Many thanks.

Sincerely,
Matt

[Andrew Musau]

Here is an example if you use LaTeX. My groups are foreign, domestic and all cars in the auto dataset and I have two estimations for each. So you can just generalize this to your example, where with 4 columns and 3 groups, your pattern is (1 0 0 0 1 0 0 0 1 0 0 0).

sysuse auto, clear
gen all = 1
estimates clear
replace weight= weight/1000
foreach var in foreign !foreign all{
    eststo: regress mpg weight disp if `var'
    eststo: regress mpg weight disp turn if `var'
}
esttab est* using mytable.tex, cells(b(star fmt(2)) t(par fmt(2))) ///
label mgroups("Foreign" "Domestic" "All cars", pattern(1 0 1 0 1 0) ///
prefix(\multicolumn{@span}{c}{) suffix(}) span erepeat(\cmidrule(lr){@span})) ///
collabels(none) replace nomtitles

// backup table and open new file
copy mytable.tex tmp.tex
file open fh using mytable.tex, write replace
// write top lines
file write fh "\documentclass{article}" _n
file write fh "\usepackage{multirow}" _n
file write fh "\usepackage{amsmath}" _n
file write fh "\usepackage{booktabs}" _n
file write fh "\usepackage{ulem}" _n
file write fh "\usepackage[table]{xcolor}" _n
file write fh "\begin{document}" _n
file write fh "\begin{table}[ht]" _n
file write fh _n "\begin{footnotesize}" _n
file write fh "\centering" _n
// now insert the table
file open fh2 using tmp.tex, read
file read fh2 line
while r(eof)==0 {
    file write fh `"`line'"' _n
    file read fh2 line
}
file close fh2
// write bottom lines
file write fh _n "\end{footnotesize}" _n
file write fh _n "\end{table}" _n
file write fh _n "\end{document}" _n
// clean up
file close fh
erase tmp.tex

[Matt Shorter]

Yes, I am using Latex and your example is perfect for my need. It would take nothing but a lot of time for me to come up with those codes and solutions. Thanks again for your kind help and answers.

Sincerely,
Matt