Exporting Tables from Stata to Excel

Exporting Tables from Stata to Excel Using putexcel

Starting from Stata 13, the putexcel function allows exporting almost any output to Excel. While this may initially require some effort, a properly stored do-file can typically be reused for other cases with minimal adjustments.

In the "National Longitudinal Surveys of Young Women and Mature Women" (NLSW) study from 1988, 2,246 respondents were surveyed about their relationship status. Stata provides a frequency table with a rather unstructured output. Even using copy & paste is challenging without additional knowledge due to number formatting issues and Stata's table limitations. While exporting tables using putexcel might seem complicated at first, it is ultimately worth the effort—especially considering that multiple formatted tables can later be generated directly from Stata with minimal additional work.

Steps for Exporting Data from Stata to Excel

First, the NLSW dataset is loaded, and an Excel file (.xlsx) named "Auswertung" is created in the current working directory using putexcel set. A worksheet named "verheiratet" is specified. It is crucial to include the replace option. If an error occurs during execution and the command is run again, this ensures that parts of the existing Excel file are properly replaced instead of being left unchanged.

sysuse nlsw88.dta
putexcel set Auswertung, sheet(„verheiratet“) replace

‍

The following section is the more challenging part of the export process, especially for beginners. Using local row = 1, all subsequent calculations and labels within the Excel file are managed without the need to manually specify individual cell references. For this example, the script has been simplified; however, fully automated table exports are entirely possible. Additionally, the global var = "married" command directly references the variable, allowing the labels and codes to be extracted automatically later.

local row = 1
global var = "married"

‍

Using the tabulate command, which stores the table results in the background only when using the matcell and matnames options, the number of rows - essentially the number of categories of the variable—is determined (local rows). Additionally, the value 0 is assigned for later percentage calculations.

tabulate married, miss matcell(freq) matrow(names)
local rows = rowsof(names)
local cum_percent = 0

‍

In this example, the table header is only set semi-automatically because a different programming approach would have been required for full automation. The cell targeting is handled using the previously defined local row, but without manual assignment.

putexcel A`row’=(„verheiratet?“) B`row’=(„N“) C`row’=(„%“) D`row’=(„kum. %“), bold hcenter
local row = `row‘ + 1

‍

The core of the programming is a loop that iterates row by row, meaning it handles the Excel export for each category of the variable. The loop runs automatically from row 1 to the last category (forvalues i = 1/rows'). It retrieves the **category labels** from the **tabulate** command using the **matrix names** (local valandlocal val_lab`). The percentages are calculated directly since these values are not stored within the matrix itself, but all relevant values are accessible (local percent_val and local cum_percent). The two final putexcel commands then execute the export of the information for a single row. Within the loop, the next row is processed by incrementing row + 1.

* Loop über alle Zeilen
forvalues i = 1/`rows‘ {
  * Berechnung der Werte
  local val = names[`i‘,1] 
  local val_lab : label ($var) `val‘
  local freq_val = freq[`i‘,1] 
  local percent_val = `freq_val’/`r(N)’* 100
  local percent_val : display %9.2f `percent_val‘
  local cum_percent : display %9.2f (`cum_percent‘ + `percent_val‘)
  * Schreiben nach Excel
  putexcel A`row’=(„`val_lab'“)
  putexcel B`row’=(`freq_val‘) C`row’=(`percent_val‘) D`row’=(`cum_percent‘)
  * Zeile hochzählen
  local row = `row‘ + 1
}

‍

Finally, the last row containing the total results is calculated by inserting an Excel formula. The table is therefore fully calculated in Excel and partially formatted (bold and horizontal centering from the initial putexcel command above).

putexcel A`row’=(„Gesamt“)
putexcel B`row’=formula(SUM(B2:B3)) C`row’=formula(SUM(C2:C3))

Summary

With sufficient knowledge of Stata programming, this syntax can be automated for virtually any table. In addition, by using loops that reference variable names and labels, it is possible to program the creation of tables covering any number of questions at once.

Considering the often labor-intensive results generated in Stata, many users faced another hurdle—at least up through Stata 12. Exporting results from the output window, which is structured much like a typewriter-style display, was only possible through additional ado-files, copy and paste, or in some cases not at all—meaning the results sometimes had to be typed out manually.