Now we could easily change our do-file into the Stata command (ado-file) called tab2xl. The solution above works well for this one table, but what if we need to export the tabulation table for 100 variables to the same Excel spreadsheet? It would be very tedious to run the same do-file 100 times, each time changing the cell and row numbers. The above commands produce this table in Excel: Local cum_percent : display %9.2f (`cum_percent' + `percent_val') Local percent_val : display %9.2f `percent_val' Putexcel A1=("Car type") B1=("Freq.") C1=("Percent") D1=("Cum.") using results, replace
Tabulate foreign, matcell(freq) matrow(names) Our do-file to produce the tabulate table in Excel looks like Last, we use forvalues to loop over the rows of the returned matrices to produce our final tables. The label function can extract the value labels for each variable, and the display function can correctly format numbers for our numeric columns. There are two extended macro functions, label and display, that can help us. With a few functions and a forvalues loop, we can easily export any table produced by running the tabulate command on a numeric variable. To get the exact table displayed in the Results window into an Excel file takes a little programming. Also our Car type column contains the numeric values (0,1), not the value lables (Domestic, Foreign) of the variable foreign, and our Percent column is not formatted correctly. column or the Total row in the export table. You probably noticed that we did not have the Cum. Putexcel A2=matrix(names) B2=matrix(freq) C2=matrix(freq/r(N)) using results,īelow is the table produced in Excel by these commands.Īgain this is a basic tabulation table. Putexcel A1=("Car type") B1=("Freq.") C1=("Percent") using results, replace
The putexcel commands used to create a basic tabulation table in Excel column 1 row 1 are tabulate foreign, matcell(freq) matrow(names) We need to use the matcell() and matrow() options of tabulate to save the results produced by the command into two Stata matrices. Tabulate is different from most commands in Stata in that it does not automatically save all the results we need into the stored results (we will use scalar r(N)). Because tabulate is not an estimation command, use the return list command to see its stored results. Load the auto dataset, and run a tabulation on the variable foreign. When I open the file corr.xlsx in Excel, the table below is displayed. Note that to export the matrix row and column names, we used the names option after we specifed the matrix r(C). Putexcel A1=matrix(r(C), names) using corr The command to type in your Stata Command window is To re-create the table in Excel, we need to export the matrix r(C) with the matrix row and column names. Let’s list the matrix r(C) to see what it contains. There is a helpful video on Youtube about the dialog here. It is easy to build the above syntax in the putexcel dialog. If you are working with matrices, the syntax is putexcel excel_cell=matrix( expression) … using filename The basic syntax of putexcel is putexcel excel_cell=( expression) … using filename Now we can use putexcel to export these results to Excel. Let’s try a simple example by loading the auto dataset and running correlate on the variables foreign and mpgīecause correlate is not an estimation command, use the return list command to see its stored results. You can list a command’s stored results after it has been run by typing ereturn list (for estimation commands) and return list (for general commands). The two main types of stored results are e-class (for estimation commands) and r-class (for general commands). Combining putexcel with a Stata command’s stored results allows you to create the table displayed in your Stata Results window in an Excel file.Ī stored result is simply a scalar, macro, or matrix stored in memory after you run a Stata command.
There is a new command in Stata 13, putexcel, that allows you to easily export matrices, expressions, and stored results to an Excel file.
Putexcel stata 12 version update#
Update 07 June 2018: See Export tabulation results to Excel-Update for new features that have been added since this original blog.