Store 3-way summary table similar to "table" command with several variables to export to latex

[Nina Caroline]

Hello all,

How do I create a Latex table that looks like a "table" table with various variables in Stata? I've found out how to create a table with one variable but not with various variables.
I'm trying to replicate the below code so that it can be updated easily and exported to latex. cat1 and cat2 are category variables and var1, var2, and var3 the variables I would like to summarize.

table cat1 cat2, c(freq mean var1 mean var2 mean var3)

Thank you!

Nina

[eric_a_booth]

ssc install tabout, replace //get -tabout- from SSC

sysuse auto, clear
table rep78 foreign, c( mean price mean mpg mean turn)


**using tabout**
tabout rep78 for using test.tex, replace ///
    style(tex) sum  ///
    c(N price  mean price mean mpg mean turn)  body

Also, check out the texsave, sutex, and texdoc packages from SSC - they are useful for exporting these sorts of tables.

[Nina Caroline]

Thank you very much! But this gives me the means by foreign and by repair status separately but I want a 3way table - by cat1 and cat2 - the exact same way the "table" table looks like.

[eric_a_booth]

Thank you very much! But this gives me the means by foreign and by repair status separately but I want a 3way table - by cat1 and cat2 - the exact same way the "table" table looks like.

-tabout- (again , from SSC) isn't designed to give this exact format of output[1], but you could reimport the output from -tabout- and then reshape/manipulate it to get this -table- output format (I generally avoid this format from -table- b/c I don't like it, but I do like to customize -tabout- output in other ways that fit my needs). I don't know of a straight forward way to export the -table- output. The 3 options I tend to use (usually OPTION 3 since I do a lot with -tabout-) are below:

Option 1: calculate the pieces (summary stats) I need using something like -tabstat- and then use loops to grab the returned results (from using the 'save' option in -tabstat-) stored in matrices and use -putexcel- to build a table, reimport the table into Stata, and then use -texsave- (again from SSC) to send it to Latex
Option 2: use -collapse- to get the stats I need, reshape the resulting dataset and export to Latex via -texsave- . This option is the shortest, but the most kludgy of the three.
Option 3: use -tabout- to get the results, import into Stata to manipulate/reshape the output, then export to Latex via -texsave-

Here is code for all 3 options I describe.
I didnt spend a lot of time optimizing the code, but each example below does mimic the -table- output you desire in a .tex Latex file .... perhaps (hopefully?) others will know of a package or more straight forward solution than these. [2]

sysuse auto, clear
table rep78 foreign, c( N price mean price mean mpg mean turn)


**OPTION 1: build using returned results & putexcel**

**headers
    putexcel A1 = (`"`:var lab rep78'"') using pe.xlsx, replace
    putexcel B1 = (`"`:var lab foreign'"') using pe.xlsx, modify
    levelsof rep78, loc(r)
    loc i = 3 
    foreach l in `r' {
        putexcel A`i' = (`"`:label rep78 `l''"') using pe.xlsx, modify
        loc i `=`i'+4'
        }
    levelsof foreign, loc(r)
    loc i = 66 
    foreach l in `r' {
        putexcel `=char(`i')'2 = (`"`:label origin `l''"') using pe.xlsx, modify
        loc i `++i'
        }
        
    
    
    foreach j in price mpg turn {
        separate `j', by(for)
    if `"`j'"' == "price" {
        loc s = 3
        loc i = 1
        tabstat `j'0, by(rep78) stat(N) save
            forval n = 1/5 {
            mat a= r(Stat`i')
            putexcel B`s' = mat(a) using pe.xlsx, modify
            loc i `++i'
            loc s `=`s'+4'
                }
            
        loc s = 3
        loc i = 1
        tabstat `j'1, by(rep78) stat(N) save
            forval n = 1/5 {
            mat a= r(Stat`i')
            putexcel C`s' = mat(a) using pe.xlsx, modify
            loc i `++i'
            loc s `=`s'+4'
                }
                
            } //end if j = price
            
        if `"`j'"' == "price" loc s = 4
        if `"`j'"' == "mpg" loc s = 5
        if `"`j'"' == "turn" loc s = 6
        loc i = 1
        tabstat `j'0, by(rep78) stat(mean) save
            forval n = 1/5 {
            mat a= r(Stat`i')
            putexcel B`s' = mat(a) using pe.xlsx, modify
            loc i `++i'
            loc s `=`s'+4'
                }
            
            
        if `"`j'"' == "price" loc s = 4
        if `"`j'"' == "mpg" loc s = 5
        if `"`j'"' == "turn" loc s = 6
        loc i = 1
        tabstat `j'1, by(rep78) stat(mean) save
            forval n = 1/5 {
            mat a= r(Stat`i')
            putexcel C`s' = mat(a) using pe.xlsx, modify
            loc i `++i'
            loc s `=`s'+4'
                }
            
            
    } //end


    *export to latex*
    import excel using pe.xlsx, clear
      
    texsave *  using pe.tex, hlines(2) ///
    title(Title Here) replace nonames   




**OPTION 2: build with collapse/reshape 
sysuse auto, clear
collapse (count) N=price (mean) price mpg turn, by(rep78 for)
decode foreign, g(for2)
drop foreign
reshape wide  N price mpg turn, i(rep78) j(for2) string
 foreach j in N price mpg turn {
    rename `j'* *`j'
    }
    rename *N *N1
    rename *price *price2
    rename *mpg *mpg3
    rename *turn *turn4

reshape long  DomesticN   Domesticprice  Domesticmpg  Domesticturn ForeignN     Foreignprice  Foreignmpg   Foreignturn ///
    , i(rep78) j(p)
aorder
order rep78 p
lab def p 1 "N" 2 "Mean Price" 3 "Mean MPG" 4 "Mean Turn", modify
lab val p p
    foreach x in Domestic Foreign {
    rename `x'N `x'
    foreach z in mpg turn price {
    replace `x' = `x'`z' if mi(`x') & !mi(`x'`z')
    drop `x'`z'
    }
    }
**
    texsave *  using test1.tex,   ///
    title(Title Here) replace    
 



**OPTION 3: again, using tabout**

**example: works with one summary stat, but not multiple**
    **limitation of tabout**
sysuse auto, clear
tabout rep78 foreign  using taboutfail.tex, replace ///
    style(tex) sum  ///
    c(mean price  )  body  
    
    
 ** piece table together and then export to latex**
 clear
 g obs = .
 sa master.dta, replace emptyok
 sysuse auto, clear
 levelsof foreign, loc(f)
  foreach x in `f' {
    
    tabout rep78 if foreign == `x'   using d`x'.txt, replace ///
      sum  ///
    c(N price mean price mean mpg mean turn )    h3(nil) mi
     **
    preserve
    insheet using d`x'.txt, clear  nonames //raw output from tabout
    rename v* v*_`x'
    g obs = _n
    order obs
        *fix missing categories for for==1
        cap replace obs = _N+2 if `"`x'"'=="1" in l
        l
    **create new header**
    set obs `=_N+1'
    replace v2 = "Foreign is `x'" in l
    replace obs = 0 in l
    sort obs
      
        merge 1:1 obs using master, nogen
        sa master.dta, replace
    restore
    }
    **export master to latex
    u master.dta, clear
     sort obs
    drop obs
    drop v1_1
    order v1_0
    texsave *  using test2.tex, hlines(2) ///
    title(Title Here) replace nonames

____________notes

[1] -tabout- produces 2-way tables for tabulations (frequencies and %'s) and for one summary statistic (e.g., mean of price by 2 categorical vars), but when you want multiple summary stats for each categorical var, as in your case, -tabout- won't do it without external manipulation). I know Ian Watson is working on a beta of a -tabout- update, perhaps it will include some more options for reshaping/cross-tabulating summarized results with more than one summary stat (?)

[2] There is a program called -logout- from SSC (author Roy Wada) that essentially logs the output in a text file and then parses/manipulates the log file to produce a results set in Stata memory that you could use -- I abandoned it after I was getting unexpected results for tables with nonstandard elements/label lengths/etc to something, but that's another option to consider.

[Chanda Chiseni]

Greetings

I have been trying to use the tabout function , but cant seem to find where the documents are being saved can anyone please help? In the case of outreg2 the files are ussually highlighted in blue and you can simply trace the file from stata but for this particular function , i have not been able to locate where the files are being saved.

Il appreciate any help

Chanda

[eric_a_booth]

It probably makes sense to start a new thread , but your files are saved in the location specified in your 'using' part of your syntax. If you don't specify a file path then your file is saved in the present working directory ( type "pwd" to see this ). If you are encountering an error, please show us an example of the syntax you ran.

For example:

sysuse nlsw88.dta, clear
tabout south race smsa  using  table2.xls, c(freq col) replace
pwd 
*saves the file in the pwd as table2.xls 
tabout south race smsa  using  `"c:\mydata\table2.xls"', c(freq col) replace
*saves in c:\mydata\
*view the location with a clickable link (like in -estout-) with this:
di `"{browse `"c:\mydata\table2.xls"'}"'