How To Export Stata Results To Excel

Update 07 June 2018: See Export tabulation results to Excel—Update for new features that have been added since this original web log.

At that place is a new command in Stata 13, putexcel, that allows you to easily export matrices, expressions, and stored results to an Excel file. Combining putexcel with a Stata command's stored results allows you to create the tabular array displayed in your Stata Results window in an Excel file.

A stored result is only a scalar, macro, or matrix stored in retentivity after you run a Stata control. The two main types of stored results are e-class (for estimation commands) and r-grade (for general commands). You can list a command's stored results afterwards information technology has been run by typing ereturn list (for estimation commands) and render list (for full general commands). Let's try a simple instance past loading the auto dataset and running correlate on the variables foreign and mpg

. sysuse car (1978 Automobile Data)  . correlate foreign mpg (obs=74)               |  foreign      mpg -------------+------------------      strange |   1.0000          mpg |   0.3934   1.0000        

Considering correlate is not an estimation control, use the return listing control to see its stored results.

. return list  scalars:                   r(N) =  74                 r(rho) =  .3933974152205484  matrices:                   r(C) :  2 ten 2        

Now we can use putexcel to export these results to Excel. The basic syntax of putexcel is

putexcel          excel_cell=(expression) … using          filename          [,          options]

If you are working with matrices, the syntax is

putexcel          excel_cell=matrix(expression) … using          filename          [,          options]

It is like shooting fish in a barrel to build the to a higher place syntax in the putexcel dialog. At that place is a helpful video on Youtube about the dialog hither. Allow's list the matrix r(C) to run into what it contains.

. matrix list r(C)  symmetric r(C)[two,2]            foreign        mpg strange          i     mpg  .39339742          one        

To copy the tabular array in Excel, we demand to export the matrix r(C) with the matrix row and column names. The command to type in your Stata Command window is

putexcel A1=matrix(r(C), names) using corr        

Note that to export the matrix row and cavalcade names, nosotros used the names option after we specifed the matrix r(C). When I open up the file corr.xlsx in Excel, the table below is displayed.

Next let'south try a more involved example. Load the auto dataset, and run a tabulation on the variable strange. Considering tabulate is non an estimation command, employ the return list command to see its stored results.

. sysuse motorcar (1978 Machine Data)  . tabulate strange     Car type |      Freq.     Percentage        Cum. ------------+-----------------------------------    Domestic |         52       70.27       seventy.27     Foreign |         22       29.73      100.00 ------------+-----------------------------------       Total |         74      100.00  . render list  scalars:                   r(North) =  74                   r(r) =  2        

tabulate is dissimilar from most commands in Stata in that it does non automatically save all the results we demand into the stored results (we volition use scalar r(N)). We demand to use the matcell() and matrow() options of tabulate to save the results produced by the command into ii Stata matrices.

. tabulate foreign, matcell(freq) matrow(names)     Auto type |      Freq.     Percent        Cum. ------------+-----------------------------------    Domestic |         52       lxx.27       70.27     Foreign |         22       29.73      100.00 ------------+-----------------------------------       Total |         74      100.00  . matrix listing freq  freq[2,1]     c1 r1  52 r2  22  . matrix listing names  names[2,i]     c1 r1   0 r2   ane        

The putexcel commands used to create a basic tabulation table in Excel cavalcade 1 row i are

putexcel A1=("Automobile blazon") B1=("Freq.") C1=("Per centum") using results, supercede putexcel A2=matrix(names) B2=matrix(freq) C2=matrix(freq/r(North)) using results,      modify        

Below is the table produced in Excel by these commands.

Again this is a bones tabulation table. You probably noticed that we did not take the Cum. column or the Total row in the export table. Too our Car type column contains the numeric values (0,1), non the value lables (Domestic, Strange) of the variable strange, and our Percent column is not formatted correctly. To go the verbal table displayed in the Results window into an Excel file takes a picayune programming. With a few functions and a forvalues loop, we can easily export any table produced by running the tabulate command on a numeric variable.

At that place are two extended macro functions, label and display, that tin aid u.s.a.. The characterization part can excerpt the value labels for each variable, and the brandish function can correctly format numbers for our numeric columns. Concluding, we use forvalues to loop over the rows of the returned matrices to produce our final tables. Our exercise-file to produce the tabulate tabular array in Excel looks similar

sysuse car tabulate strange, matcell(freq) matrow(names)  putexcel A1=("Car type") B1=("Freq.") C1=("Percent") D1=("Cum.") using results, replace  local rows = rowsof(names) local row = ii local cum_percent = 0  forvalues i = 1/`rows' {          local val = names[`i',1]         local val_lab : label (foreign) `val'          local freq_val = freq[`i',one]          local percent_val = `freq_val'/`r(Due north)'*100         local percent_val : display %9.2f `percent_val'          local cum_percent : display %nine.2f (`cum_percent' + `percent_val')          putexcel A`row'=("`val_lab'") B`row'=(`freq_val') C`row'=(`percent_val') ///                 D`row'=(`cum_percent') using results, alter         local row = `row' + 1 }  putexcel A`row'=("Total") B`row'=(r(Northward)) C`row'=(100.00) using results, modify        

The in a higher place commands produce this table in Excel:

The solution above works well for this one table, but what if nosotros need to export the tabulation table for 100 variables to the aforementioned Excel spreadsheet? It would exist very irksome to run the same do-file 100 times, each fourth dimension changing the jail cell and row numbers. At present we could easily modify our practice-file into the Stata command (ado-file) chosen tab2xl. The syntax for our new command could be

tab2xl          varname          using          filename, row(rownumber) col(colnumber) [replace canvass(name)]        

The pseudocode of our program (file tab2xl.ado) looks like

programme tab2xl   /* parse command syntax */    /* tabulate varname */    /* get cavalcade letters based on starting column number passed in */    /* write header row to filename in starting row number passed in */    /* loop over rows of returned matrix and summate/write values to filename */    /* write total row to filename */ end        

If you would like to download a working version of our tab2xl command, type

net install http://www.stata.com/users/kcrow/tab2xl        

in Stata.

Source: https://blog.stata.com/2013/09/25/export-tables-to-excel/

Posted by: mcclearylonswellot.blogspot.com

Share this post