*! version 1.7 2017-04-20

Title

tw3xls -- Export tabulation results of three or four variables to Excel with a nice formatting

Syntax

tw3xls
rowvar colvar supercolvar
[if] [in]
[using filename]
[,
by(groupvar)
show
total(rows cols)
sort(low|high)
top(#)
missing(#)
format(basic merge)
stub(string)
sheet(string)
replace
modify
]

Main
using specify an export file name; can be used with or without file extension

Options
by(groupvar) a grouping variable to generate a series of three-way tables for each level of groupvar

show display corresponding tabulation with a help of a -table- command

total(rows cols) add an additional column and/or row with totals. Both options can be applied at the same time

sort(low|high) sort tabulation results in ascending (low) or descending (high) order

format(basic merge) format output tables. 'basic' option adds borders, alignment of cells and merging of header cells. 'merge' merges supercolumn header cells

top(#) select top # values in the output table(s). Default is descending order. Can be combined with a sort() option to select top # values in ascending order

missing(#) replace cells with zero frequencies to a numeric value #. By default zero frequency cells will be blank in Excel.

stub(string) save tabulation results into Stata matrices with a string prefix

sheet(string) name of the sheet to save tabulation results; default is "Data"

replace overwrite the export file; default option

modify keep previous data in the export file but overwrites data in a sheet: on a specific sheet or on a default sheet "Data" if no sheet name is specified

tw3xls works faster while saving as *.xls though formatting may be broken on a large number of tables.
Saving as *.xlsx correctly displays all formatting but works slower.

Description

tw3xls serves to save three-way frequency tables into Excel with an appropriate formatting.
It is very useful when dealing with a large number of tables, especially when the tables should be pre-formatted.
The output will be written to a spreadsheet file format (usually *.xls or *.xlsx). If you want to work with the tables further, they are saved in rclass and can be put into Stata matrices named by stub(string).
Number of variables is limited to three, you can add the fourth variable in by(groupvar) option which will produce a set of three-way tables: one for each level of the fourth variable.

Note: when saving as *.xls the program works much faster comparing to saving as *.xlsx, however the formatting may be broken for some tables. Saving as *.xlsx fixes that issue but requires more time to proceed.

(Go up to Syntax)

Options description

Options

by(groupvar) adding this option produces a set of three-way tables for each unique value of the groupvar.
The groupvar can be both string or numeric. When using this option, each output table will have a header indicating a corresponding groupvar value.

show display tabulation results in the Output window as if Stata's -table- command is used.

total(rows cols) add an additional column and/or row with totals to the output table(s).
The suboptions can be abbreviated to r, ro, row and c, co, col. Both suboptions can be applied at the same time

sort(low|high) sort tabulation results in ascending (lowest to highest) or descending (highest to lowest) order.
Only one sorting option can be used at a time. Can be abbreviated down to l for low and h for high.

format(basic merge) add formatting to output tables.
basic option (also can be written as b, ba, bas) adds borders around all cells, vertical and horizontal alignment
and merges all headers except supercolumns. merge option (m, me) merges table supercolumns in the export file.
Both suboptions can substantially increase computation time: basic by ~10%, merge by ~80%

top(#) select first # rows for each tabulation result based on total row values. Values are sorted in descending order by default. To get a reverse order, must be combined with a sort(low) option. When using in conjunction with total(cols) the last row will contain total values for a complete table.

missing(#) replace cells with zero frequencies to another numeric value #.
If none is specified, all cells with zero frequency will be blank.

stub(string) save output tables into matrices with a string prefix,
i.e. stub(mat) will produce a series of matrices: mat1 when there is no by(groupvar) option and mat1, ..., matk when by(groupvar) is used.

sheet(string) name a worksheet to export the data as string. When no sheet name is specified, a default name is used: "Data".
This option may be useful when saving multiple tabulation results into one file but on different tabs.

replace replace an output file; if modify is not specified that would be a default option.

modify modify an output file; useful when saving several tabulation results into one file but on different tabs using sheet(string) option.

(Go up to Syntax)

Examples

Use 1978 Automobile Dataset:

. sysuse auto

Convert encoded variable to a string to get string labels in the output file instead of numbers:

. decode foreign, gen(foreign_str)

Save a three-way tabulation into example1.xlsx; save output table into matrix "ms", replace missing values (zero frequencies) with ".", show corresponding tabulation using Stata's -table- command:

. tw3xls mpg rep78 foreign using example1, stub(ms) missing(.) show

Modify the same file, add a sheet "Data2" on which a set of three-way tables is stored for each level of the "headroom" variable, and replace all zero-frequency cells values with 0:

. tw3xls mpg rep78 foreign_str using example1, by(headroom) stub(ms) mi(0) show sheet(Data2) modify

Replace the current file, add row and column totals, sort each table in a descending order:

. tw3xls mpg rep78 foreign_str using example1, by(headroom) total(c r) sort(h) replace

Same as previous, but adds formatting: basic (borders, cell alignment) and merge (merge column labels and center them vertically):

. tw3xls mpg rep78 foreign_str using example1, by(headroom) total(c r) sort(h) form(b m) replace

Select first 5 rows in descending order:

. tw3xls mpg rep78 foreign_str using example1, by(headroom) total(c r) form(b m) top(5) replace

(Go up to Syntax)

Remarks

1. tw3xls saves only frequency tables.

2. tw3xls works with both string and numeric data. For encoded variables please create string variables via -decode- - it stores labels as text instead of numbers in the output file.

3. For a fast export don't use -format()- options: that will save tables and headers as a plain text without formatting and significantly increase computation speed.

4. Output files with the same name will be overwritten by default unless -modify- is specified.

5. Alternatives/similar programs:

a) For three-way tables: -tab3way- in conjunction with -logout-.

b) For one/two-way tables: -tabout-, -tab2xl- and -tab2xl2-. The latter command can be found here: https://github.com/leoshibata/tab2xl2/

Author

Andrey Ampilogov, Freelancer, a.ampilogov@gmail.com