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
}

‍

‍Abschließend wird die letzte Zeile mit den Ergebnissen der Summe über das Setzen einer Excel-Formel berechnet. Die Tabelle ist damit vollständig in Excel berechnet und teilweise formatiert (bold bzw. hcenter aus dem ersten putexcel-Befehl oben).

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

Zusammenfassung

Mit ausreichenden Kenntnissen im Bereich der Stata-Programmierung lässt sich diese Syntax auf beliebige Tabellen automatisieren. Außerdem ist hinsichtlich Variablennamen und –labels eine Programmierung über eine Schleife zur Erstellung von Tabellen über beliebig viele Fragen auf einmal möglich.

Mit Blick auf die oftmals mühsam erstellten Ergebnisse in Stata erreichten manche Nutzer bis Stata 12 oftmals eine weitere Hürde. Den Export – aus dem wie eine Schreibmaschine aufgebautem Output Fenster – zu kopieren, ging entweder nur über zusätzliche ado-Files, copy & paste oder oftmals auch gar nicht, so dass die Ergebnisse im letzteren Fall mühsam abgetippt werden mussten.