How To: Move Data Tables from STATA to Excel

Every once in a while I decide it is about time to put something useful on this blog. After roughly 8 hours of coding and assorted other meddling with data, I thought it might be worth my time to throw up a quick description of the easiest way I have found to export data tables from STATA to Excel (Windows 7 Version & Above). I vastly prefer formatting my tables in Excel and the system is better integrated with word so if you are still rocking STATA (yeah, I get it… R is the way of the future), here is a quick and easy way to get those big unruly tables into an easy to use format. Enjoy!

Note1: Click on the images to zoom in!

Note2: Everything with a bullet is a STATA command that you can copy and paste (of course you will need to fill in the “blanks” with actual variable names (varname) & values & filenames (filename) where applicable.

STEP 1. Generate the Table

  • list varname1 varname2 varname3 if varname5==0, clean nolabel

OPTIONS:

  • Help list

Clean = no table lines in the stata output

Table = table lines in the stata output

Nolabel = no value labels in the stata output (if you do not include this, value labels will be included automatically)

STEP 2. Export the Table

  • outsheet varname1 varname2 varname3 if varname5==0 using filename.csv, comma nolabel

OPTIONS:

  • Help outsheet

Comma = places a comma between fields in the exported table (can easily be separated out again using excel’s “Text to Columns” feature in the “Data” tab

Nolabel = no value labels in the table output (if you do not include this, value labels will be included automatically)

STEP 3. Edit the Table in Excel

The table that you have exported will be very easy to use in Excel because you exported it as a .csv (comma separated value) file. For more information on .csv files, please see: http://en.wikipedia.org/wiki/Comma-separated_values.

1. Click the cell where you want to put the data from the text file.

2. On the Data tab, in the Get External Data group, click From Text.

3. Select the file that you have just created (“filename.csv”). This step automatically opens up the Text Importation Wizard

Step 1 of 3: Make sure “Delimited” is selected, and hit “Next >

Step 2 of 3: Make Sure “Comma” is the only option selected, and click “Next >

Step 3 of 3: Chose the relevant column data format for each of the columns (note: I hate it when non-dates convert to dates in Excel if you have a “-“ between numbers. Here is where you can fix that. Alternatively, you may want to make something auto-format into a date here if it was in STATA date format before). Click “Finish

4. At the Import Data Screen, chose where you want to place your data (in this worksheet? Where should the data start? [note: =$A$1 will start the data at the top left-hand corner of the current sheet]). Click “OK”.

TA-DAH!

Advertisements

5 responses to “How To: Move Data Tables from STATA to Excel

  1. Thanks, this might be pretty useful. If you’re in a place where you have access to it, StatTransfer can move data from Stata to Excel (http://www.stattransfer.com/stattransfer/formats.html), and might be a better option. But I currently do not, so I may use your approach instead. I also don’t know how well StatTransfer handles the stupid date-formatting stuff that Excel tends to do. But I think it writes actual Excel files instead of CSVs, which is an advantage.

    If you’re trying to write tables of results to Excel, rather than whole datasets, there are a number of packages to do that. estout is probably the best for regression tables, outreg2 is good too. In my experience all of them are fairly terrible for making formatted tables of summary statistics (as opposed to regression results). Since something like 75% of the tables I make are just means and so forth, I wrote my own .do file to make these. I am willing to share it but not to post it online (yet).

    I also disagree that R is the way of the future. That’s only true if you want to run stuff very slowly, and code in a way that’s extremely general but makes simple tasks unnecessarily complicated. I’ve used it in the past and am retired from R coding, hopefully for life. I guess I prefer it to SAS, but not by very much.

    • Apparently newer versions of STATA have some sort of automated export system to excel but I don’t have access to that. Either way, mysystem is quick and painless and there isn’t really any need to find other packages or software systems to streamline the process.

      If you don’t want to use the external data grab wizard (for whatever reason), you can copy & paste your .csv files into Excel and they will self-format after the first time. To do this you just enter the command “type filename.csv”, copy the result & paste into word. The first time you may need to highlight the table in word, chose “text to columns” in the data tab and follow Step 3 Part1-3 of 3 above. After that, it’s all shiny.

      I’m not really an R user but people seem to be pretty big on it in Poli Sci so I may have to learn at some point. It has to be better than SPSS, right? (that’s the p.o.s. software that we inexplicably taught to our undergrad students at UBC). Shockingly bad.

  2. Pingback: How To: Streamline the Graduate Student Office – Tech Advice | Academic Progress Goes "Clunk"·

  3. Pingback: Thoughts on Going/Leaving Home | Academic Progress Goes "Clunk"·

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s