help tabsheet

Title

tabsheet -- Rapidly outputs cross-tabular statistics to a tab-delimited file, which can be opened in a spreadsheet editor such as Excel.

Process

tabsheet enables the rapid production of cross-tabular statistics to spreadsheets that may contain hundreds or thousands of rows.
Each row, r = 1, 2, ..., R, will represent a metric for which point estimates are desired for each of c = 1, 2, ..., C subpopulations.
By default, for row r and subpopulation c, there will be three columns, representing: (1) the proportion or mean of r among subpopulation c; (2) the associated standard error estimate; and (3) the associated n.

Users may specify their desired outputs across several executions of tabsheet, rather than being required to specify everything in a single (very long) line of code.
Further, instead of requiring users to specify the output filename and use of weights in every tabsheet command, tabsheet requires that the user specifies them a single time upfront.
Specifically, the user opens an output file for writing with the file open command and file handle outfile and specifies the survey weights (or lack thereof) using the svyset command.
Next, the user executes tabsheet command(s), after which the file writing process is concluded with a file close command.
Finally, the user opens the output file using spreadsheet software.

Thus, tabsheet is envisioned as part of the following process:

1. Set up

a. Open dataset

b. Specify survey design (or lack thereof) using svyset

c. Conduct recodes (as applicable)

i. Create column variables, i.e., dichotomous variables indicating subpopulations of interest

ii. Conduct any relevant recodes of row variables for which proportions or means are desired

2. Create output file

a. Open file for writing using file open with file handle outfile

b. Run tabsheet commands to output column headers, means, and/or proportions, as applicable

c. Close output file using file close outfile

3. Open output file as spreadsheet

Syntax

tabsheet varlist [if] [in], subpops(subpop_varlist) type(string) [options]

subpops(subpop_varlist) specifies the variable list of subpopulation indicator variables for which you want statistics.
All values of subpopulation variables should be 1, 0, or missing.
Subpopulation membership is identified by a value of 1. Cases with missing subpopulation data will be excluded from variance estimates.

type(string) specifies the type of function you would like to call or type of variable for which you want statistics:

type(header) is used for cosmetic purposes in labeling the output.
This type of tabsheet command will output column labels with variable names (i.e., indicating subpopulations), and will distinguish columns containing proportions/means, estimated SEs, and sample sizes, as applicable.
Note: variables should be placed in 'varlist' rather than in 'subpop_varlist'; the subpops option is not required for commands of type(header).

type(prop) outputs proportions for every possible value of the variable(s).

type(line) outputs one line with the proportion of cases giving an
option of '1' for each variable (e.g., for a series of related dichotomous variables; in the survey context, this may be a battery of select-all-that-apply question items).
Note: the denominator is all cases with a non-missing value.

type(mean) outputs a row of means for each variable.

NOTE: tabsheet requires that the survey design variables (e.g., weights) be identified using svyset.
For unweighted estimates, simply run svyset and specify "none" for weights.
For example, an unweighted dataset could be specified using: svyset _n, vce(linearized) singleunit(missing).

NOTE: tabsheet requires that an output file be open with the handle outfile. For example, this can be done with: file open outfile using "output.txt", write replace.
After the conclusion of all tabsheet commands, the user will run the command file close outfile.

NOTE: Successful tabsheet commands write to the output file, but do not display results in the display window. The display window is reserved by tabsheet for warnings and errors.

Options

simple produces streamlined output (one column per subpopulation variable, instead of three columns per subpopulation variable).
This is recommended if it is not necessary to estimate standard errors. Note: with this option enabled, tabsheet will only output sample sizes for type(prop).

header(varlabel) displays headers for each subpopulation, using the subpops' variable labels as column titles.

header(varname) is similar to header(varlabel), except that the column headers are the unlabeled variable names instead of the variable labels.

suppress(numbers) will exclude observations with a particular set of values from the denominator; numbers can be suppressed individually, or a range can be expressed with the "/" symbol, with the lower number listed first.
E.g., if the values between -90 and -100 are reserved for various forms of missing survey data, and responses of "don't know" are given values of 99, then supp(-100/-90 99) will exclude all such observations from calculations.

lineval(numbers) allows you to use type(line) with a custom set of values. E.g., lineval(4/5) outputs a row for each variable with the proportion of values between 4 and 5 for each subpopulation.

sort allows type(line) commands to be sorted in descending order, based on the percentages for the first listed subpopulation.

overse causes the standard errors to be calculated via the over option rather than via the subpop option.
Use of this option does not impact point estimates but, under certain scenarios, can have a minor impact on variance estimates.

nobreak does not output an extra line break after a given command; by default, there is an extra line break at the end of every command, to separate different pieces of output.

Description

tabsheet facilitates the rapid generation of cross-tabular statistics. After receiving a list of variables and subpopulations,
it automatically outputs proportions, means, estimated standard errors, and sample sizes to a tab-delimited file, which can be opened as a
spreadsheet in software such as Microsoft Excel.

Compared with other analogous Stata programs, tabsheet's strengths include:

-its simplicity and ease of use

-the ability to "mix-and-match" different types of statistics in a single exported spreadsheet across runs

-its accommodation of svy data

-its options for computing statistics for several subpopulations simultaneously

-its novel options for providing consolidated output for a series of dichotomous variables via the type(line) option

Example 1(a): Basic data setup, tabsheet syntax

/* Clear environment, open dataset, and declare the survey design */

clear all

use http://www.stata-press.com/data/r9/gss1991.dta

svyset _n, vce(linearized) singleunit(missing)

/* Modify the variable labels to include the variable name, to make the output easier to read */

foreach v of varlist sex-work9 {

label variable `v' "`v'. `:variable label `v''"

}

/* Create dummy variables to indicate subpopulation membership */

gen total = 1

gen male = sex==1

recode age (18/34=1) (else=0), gen(age18to34)

recode age (35/49=1) (else=0), gen(age35to49)

recode age (50/64=1) (else=0), gen(age50to64)

recode age (65/99=1) (else=0), gen(age65plus)

/* Create macro listing subpop variables for use */

local subpops "total male female age18to34-age65plus"

/* Open the output file */

file open outfile using "myoutput1a.txt", write replace

/* Write the header line listing the subpopulations and labeling columns that have proportions/means, estimated SEs, and sample sizes */

tabsheet `subpops', type(header)

/* For each variable educ-speduc, output a line indicating the item mean for every subpopulation, along with estimated SEs and sample sizes */

tabsheet educ-speduc, sub(`subpops') type(mean)

/* For the next few variables, output a line with the variable label, then output a line for every value label with the proportions,
estimated SEs, and sample sizes for that value */

tabsheet tax-helpoth, sub(`subpops') type(prop)

/* For each variable hlth1-hlth9, output a line with the proportions that give an option of 1 (e.g., for dichotomous variables) */

tabsheet hlth1-hlth9, sub(`subpops') type(line)

/* Do the same as above for the next variable group, but sort it in descending order */

tabsheet work1-work9, sub(`subpops') type(line) sort

/* Do the same as the above lines, but with the simple option enabled.
By default, there are three columns for every variable: proportion/mean, estimated SE, and n.
The simple option only displays one column for each subpopulation (with the proportion or mean), and adds a line at the end with the sample sizes. */

tabsheet `subpops', type(header) simple

tabsheet educ-speduc, sub(`subpops') type(mean) simple

tabsheet tax-helpoth, sub(`subpops') type(prop) simple

tabsheet hlth1-hlth9, sub(`subpops') type(line) simple

tabsheet work1-work9, sub(`subpops') type(line) sort simple

/* Close the output file */

file close outfile

Example 1(b): Recodes

Given that tabsheet provides cleaner output if the variables and values are all labeled, the below examples demonstrate the use of basic data cleaning steps in conjunction with tabsheet:

/* Conduct recodes with value labeling within line */

recode obey-helpoth (1/2=1 "Important") (3=2 "Somewhat important") (4/5=3 "Not important"), pre(r_) label(imp_lbl)

/* Conduct recodes with separate value labeling lines */

recode educ-speduc (0/11=1) (12=2) (13/15=3) (16/20=4), pre(r_)

label define eduLabel 1 "1: Less than HS" 2 "2: HS grad" 3 "3: Some college" 4 "4: College graduate"

label values r_educ-r_speduc eduLabel

/* Create variable labels for the recoded variables */

foreach var of varlist obey-helpoth educ-speduc {

local varlabel`var': variable label `var'

label variable r_`var' `"r_`varlabel`var''"'

}

/* Open output file */

file open outfile using "myoutput1b.txt", write replace

/* Output a header and then proportions for the recoded variables */

tabsheet RegionNE RegionSE, type(header)

tabsheet r_obey-r_helpoth r_educ-r_speduc, subpops(RegionNE RegionSE) type(prop)

/* Close output file */

file close outfile

Example 2: Advanced tabsheet commands

/* Prepare dataset for this example */

clear all

use http://www.stata-press.com/data/r9/gss1991.dta

svyset _n, vce(linearized) singleunit(missing)

cap gen total = 1

recode hlth* (2=0)

forval i=1/9 {

label define hlth1 0 "no", modify

}

/* Open output file */

file open outfile using "myoutput2.txt", write replace

/* Create headers and output proportions the basic way */

tabsheet total hlth1-hlth9, type(header)

tabsheet obey-helpoth, sub(total hlth1-hlth9) type(prop)

/* Do the same as above, but in one line */

tabsheet obey-helpoth, sub(total hlth1-hlth9) type(prop) header(varname)

/* Do the same as above, but with headers displaying the variable labels (instead of variable names) */

tabsheet obey-helpoth, sub(total hlth1-hlth9) type(prop) header(varlabel)

/* Now, output one line for each item, with the percentage indicating that the item is the most important or second most important */

tabsheet obey-helpoth, sub(total hlth1-hlth9) type(line) header(varlabel) lineval(1/2)

/* Do the same as above, but sort the output in a descending fashion, with the highest percentages for the first subpop listed on top */

tabsheet obey-helpoth, sub(total hlth1-hlth9) type(line) header(varlabel) lineval(1/2) sort

/* In order to demonstrate the suppress option, let's artifically induce some missing data with non-substantive values of -99 and -91... */

foreach v of varlist obey-helpoth {

replace `v' = -99 if mod(_n,40)==1

replace `v' = -91 if mod(_n,27)==1

}

/* We could calculate the row percentages of all cases, including missing data in the denominator, via the syntax below, although this will cause some underestimates of population parameters */

tabsheet obey-helpoth, sub(total hlth1-hlth9) type(line) header(varlabel) lineval(1/2) sort

/* Alternatively, we can remove the missing values from the denominator, which should produce point estimates similar to those from before the missingness was induced */

tabsheet obey-helpoth, sub(total hlth1-hlth9) type(line) header(varlabel) lineval(1/2) sort suppress(-99 -91)

/* Close output file */

file close outfile

Opening Output in Microsoft Excel

First, after running the syntax, ensure that the output file has been properly closed in Stata (e.g., by running the command file close outfile).
Then, locate the output file in Windows Explorer. Right click the output file, and click "Open with-->Excel."
If Excel is not listed as an option, click "Open with-->Choose default program" and then click "Browse" to locate the Excel.exe file.
An example location for the Excel.exe file is C:\Program Files (x86)\Microsoft Office\Office14\Excel.EXE.

Technical Notes

-Proportions and means are all computed using the "subpop" option by default (instead of using the "over" option).

-Option simple only outputs sample sizes for type(prop). This is because type(line) and type(mean) could potentially have different sample sizes for a set of variables that are displayed together.

-Cases with missing data are not removed listwise.
E.g., with the command tabsheet v1-v5, type(prop), a case that has missing data for v5 could be included for other items.
By contrast, this is not the case for the command svy: proportion v1-v5.

User Tips

-Give careful thought to the handling of invalid values via recoding them to missing or using the suppress(numbers) option, particularly when using the type(line) and type(mean) options.

-Discrepancies in standard errors may come up between different analysts due to inappropriate coding of subpop variables.
In most cases, subpop variables should be coded as 1 for cases in the subpopulation, 0 for cases outside of the subpopulation, and missing for cases that should be excluded from analysis.
See the 2008 Stata Journal article by West, Berglund, and Heeringa for details.

-Make sure to read your output in the Stata console, in case of warnings for possible user error, such as using type(line) with a variable with more than two nonmissing values.

-Note that due to rounding, Stata's displayed output may differ slightly from tabsheet output.

-One user error could cause entire columns or rows to be incorrect.
Thus, I recommend that you check recodes thoroughly and inspect an entire column and entire row of output using built-in Stata commands to ensure that output is as expected.

Acknowledgements

Thank you to Fors Marsh Group (FMG) for support in developing this program, the internal version of which is titled topline and is functionally equivalent.
Thanks also to Joe Luchman for help testing several versions of topline and providing helpful feedback, Jen Gibson for her help testing a previous version, and Sarah Evans for helping to popularize previous versions within FMG.
Thank you to several others at FMG who shared feedback on previous versions, shared project syntax for testing purposes, encouraged a public release of this program, and provided editing support.

Author

Jonathan Mendelson, jmendelson@gmail.com