Replace 07 June 2018: See Export tabulation outcomes to Excel—Replace for brand spanking new options which have been added since this authentic weblog.
There’s a new command in Stata 13, putexcel, that means that you can simply export matrices, expressions, and saved outcomes to an Excel file. Combining putexcel with a Stata command’s saved outcomes means that you can create the desk displayed in your Stata Outcomes window in an Excel file.
A saved outcome is solely a scalar, macro, or matrix saved in reminiscence after you run a Stata command. The 2 principal sorts of saved outcomes are e-class (for estimation instructions) and r-class (for basic instructions). You may listing a command’s saved outcomes after it has been run by typing ereturn listing (for estimation instructions) and return listing (for basic instructions). Let’s attempt a easy instance by loading the auto dataset and operating correlate on the variables overseas and mpg
. sysuse auto
(1978 Car Information)
. correlate overseas mpg
(obs=74)
| overseas mpg
-------------+------------------
overseas | 1.0000
mpg | 0.3934 1.0000
As a result of correlate isn’t an estimation command, use the return listing command to see its saved outcomes.
. return listing
scalars:
r(N) = 74
r(rho) = .3933974152205484
matrices:
r(C) : 2 x 2
Now we will use putexcel to export these outcomes to Excel. The fundamental syntax of putexcel is
putexcel excel_cell=(expression) … utilizing filename [, options]
If you’re working with matrices, the syntax is
putexcel excel_cell=matrix(expression) … utilizing filename [, options]
It’s straightforward to construct the above syntax within the putexcel dialog. There’s a useful video on Youtube concerning the dialog right here. Let’s listing the matrix r(C) to see what it accommodates.
. matrix listing r(C)
symmetric r(C)[2,2]
overseas mpg
overseas 1
mpg .39339742 1
To re-create the desk in Excel, we have to export the matrix r(C) with the matrix row and column names. The command to kind in your Stata Command window is
putexcel A1=matrix(r(C), names) utilizing corr
Be aware that to export the matrix row and column names, we used the names possibility after we specifed the matrix r(C). Once I open the file corr.xlsx in Excel, the desk under is displayed.
Subsequent let’s attempt a extra concerned instance. Load the auto dataset, and run a tabulation on the variable overseas. As a result of tabulate isn’t an estimation command, use the return listing command to see its saved outcomes.
. sysuse auto
(1978 Car Information)
. tabulate overseas
Automobile kind | Freq. P.c Cum.
------------+-----------------------------------
Home | 52 70.27 70.27
International | 22 29.73 100.00
------------+-----------------------------------
Complete | 74 100.00
. return listing
scalars:
r(N) = 74
r(r) = 2
tabulate is totally different from most instructions in Stata in that it doesn’t mechanically save all the outcomes we’d like into the saved outcomes (we’ll use scalar r(N)). We have to use the matcell() and matrow() choices of tabulate to save lots of the outcomes produced by the command into two Stata matrices.
. tabulate overseas, matcell(freq) matrow(names)
Automobile kind | Freq. P.c Cum.
------------+-----------------------------------
Home | 52 70.27 70.27
International | 22 29.73 100.00
------------+-----------------------------------
Complete | 74 100.00
. matrix listing freq
freq[2,1]
c1
r1 52
r2 22
. matrix listing names
names[2,1]
c1
r1 0
r2 1
The putexcel instructions used to create a fundamental tabulation desk in Excel column 1 row 1 are
putexcel A1=("Automobile kind") B1=("Freq.") C1=("P.c") utilizing outcomes, change
putexcel A2=matrix(names) B2=matrix(freq) C2=matrix(freq/r(N)) utilizing outcomes,
modify
Beneath is the desk produced in Excel by these instructions.
Once more it is a fundamental tabulation desk. You in all probability seen that we didn’t have the Cum. column or the Complete row within the export desk. Additionally our Automobile kind column accommodates the numeric values (0,1), not the worth lables (Home, International) of the variable overseas, and our P.c column isn’t formatted appropriately. To get the precise desk displayed within the Outcomes window into an Excel file takes a bit programming. With just a few features and a forvalues loop, we will simply export any desk produced by operating the tabulate command on a numeric variable.
There are two prolonged macro features, label and show, that may assist us. The label operate can extract the worth labels for every variable, and the show operate can appropriately format numbers for our numeric columns. Final, we use forvalues to loop over the rows of the returned matrices to provide our last tables. Our do-file to provide the tabulate desk in Excel appears like
sysuse auto
tabulate overseas, matcell(freq) matrow(names)
putexcel A1=("Automobile kind") B1=("Freq.") C1=("P.c") D1=("Cum.") utilizing outcomes, change
native rows = rowsof(names)
native row = 2
native cum_percent = 0
forvalues i = 1/`rows' {
native val = names[`i',1]
native val_lab : label (overseas) `val'
native freq_val = freq[`i',1]
native percent_val = `freq_val'/`r(N)'*100
native percent_val : show %9.2f `percent_val'
native cum_percent : show %9.2f (`cum_percent' + `percent_val')
putexcel A`row'=("`val_lab'") B`row'=(`freq_val') C`row'=(`percent_val') ///
D`row'=(`cum_percent') utilizing outcomes, modify
native row = `row' + 1
}
putexcel A`row'=("Complete") B`row'=(r(N)) C`row'=(100.00) utilizing outcomes, modify
The above instructions produce this desk in Excel:
The answer above works nicely for this one desk, however what if we have to export the tabulation desk for 100 variables to the identical Excel spreadsheet? It might be very tedious to run the identical do-file 100 instances, every time altering the cell and row numbers. Now we may simply change our do-file into the Stata command (ado-file) referred to as tab2xl. The syntax for our new command might be
tab2xl varname utilizing filename, row(rownumber) col(colnumber) [replace sheet(name)]
The pseudocode of our program (file tab2xl.ado) appears like
program tab2xl /* parse command syntax */ /* tabulate varname */ /* get column letters based mostly on beginning column quantity handed in */ /* write header row to filename in beginning row quantity handed in */ /* loop over rows of returned matrix and calculate/write values to filename */ /* write whole row to filename */ finish
If you need to obtain a working model of our tab2xl command, kind
internet set up http://www.stata.com/customers/kcrow/tab2xl
in Stata.
