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
- 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
- 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”.