Importing and exporting data files

We often find data on the Web in formats other than Stata, or we need to share data with our collaborators who (unfortunately) are not Stata users.

There are several ways to get data into and out of Stata. While this list is probably not exhaustive, it covers most of the formats that we encounter at CPC. Some methods are available from within Stata, while others require shareware or commercial software.

Stata commands

fdause and fdasave

These are native Stata commands to read and write SAS transport (xport) format data files. This is the preferred format for submitting pharmaceutical test data to the US Food and Drug Administration, hence the "fda" in the command name. See the help for details on handling value labels (called "formats" in SAS).

usespss and desspss

These commands are available from the SSC archive. They handle Windows SPSS data files with the .sav suffix (called "system" files in SPSS). They do not handle SPSS portable files with the .por suffix. See usesas and Stat/Transfer below for software that can deal with SPSS portable files.

outsheet and insheet

The outsheet command is a way to write the variable names and values (but no labels) into tab-delimited text files, comma-separated values (.csv) files, or files with other delimiters. Most software will read a text file with one of these delimiters between the values. Stata also reads these text files with the insheet command. So, you can export data from something like MySQL to a .csv file and read it into Stata with insheet.

xmluse and xmlsave

These commands transfer data between Stata and MS Excel's xml format. The xml format is a portable text version of Excel's .xls or .xlsx format files. xmlsave produces a file that Excel reads directly. In order to use an Excel file in Stata, though, you need to open it in Excel and save it as type xml. There are two xml "Save As" formats in Excel 2007. The "XML spreadsheet 2003 (*.xml)" option seems to work better. Here's an example:

xmluse c:\tables\workbook.xml", doctype(excel) sheet("table 2") cells(a3:k79) firstrow

See Exporting Stata Results to MS Office for an example of xmlsave

Other software

usesas and savasas

While these are implemented as Stata commands, they're listed here under "Other software" because they both require you to have SAS running locally as well as Stata. At CPC we have both of these commands running on both the Windows and Linux systems, and they work wonderfully.

If you have a standalone computer with both SAS and Stata installed, you may want to download these commands from the SSC archive. You may need to edit the code to tell Stata where to find the SAS command, but that's all explained in the help and in comments inside the code.

Stat/Transfer

This is not intended as an advertisement for the product, but it really is useful. In converts data files between statistical software, spreadsheets, databases, etc. with ease. If you find yourself needing to import or export data across software formats often, it's worth considering. It's distributed by Stata Corp.

Review Again?
Another topic?

---

Questions or comments?  If you are affiliated with the Carolina Population Center, send them to Phil Bardsley.