Economics should be open

December 2, 2008

MS Access Tables to Stata, for Residential Energy Consumption Survey

Problem: To convert MS Access tables (of the EIA Residential Energy Consumption Survey 1997 data) to CSV files for STATA import

This is also a general script for converting MS Tables to CSVs

Solution: Wrote a VBA script


  1. Open MS Access
  2. Tools | Macros | Visual Basic Editor
  3. Create a new module on your database by doing: Right Clickon database | Insert | Module
  4. Copy the following text (without the line numbers)
    1. Sub ExportAllTablesCsv()
    2. Dim dbMyDB As Database
    3. Set dbMyDB = OpenDatabase(“recs97_converted.mdb”)
    4.     For Each tdfCurrent In dbMyDB.TableDefs
    5.         fileoutname = “C:\” & tdfCurrent.Name & “.csv”
    6.         If Left(tdfCurrent.Name, 2) <> “MS” Then
    7.             DoCmd.TransferText acExportDelim, , tdfCurrent.Name, fileoutname, TRUE
    8.         End If
    10.     Next
    11. End Sub
  5. For your customization:
    1. change your mdb file to your mdb file
    2. The If statement (line 10) is there to deal with the fact that there are certain system tables that I needed to skip. None of my tables started with “MS”, so this was a simple non-general fix.
    3. If you look up help for TransferText it has a SpecificationName. I left that argument blank. You can change to tabs or other things using that.
    4. I had trouble with getting column headings, but jwhite at showed me the light. This code now gives column headings with the TRUE argument.  For more details, check out

If you can think of a better solution, I’d love your thoughts!

Other possible/failed solutions:

  • I tried running a query that joined all the tables with the EIA ID Num as the key for each table. Though this was easy, I got the “too many fields” error.
  • I think there is a way to use SQL (or something???) to select all the tables and write them and to import those into another program. STATA, to my limited knowledge, doesn’t do SQL. 
  • STATA supposedly does XML. One can use TransferText to write XML too. I don’t know XML well enough to try it.

Any comment appreciated.





November 14, 2008

STATA: Generating a bunch of lagged variables

Filed under: coding, Stata — Tags: , — howardchong @ 10:23 pm

This small blog post is just a note on how to create a bunch of lagged variables using a simple forvalues loop.

* this gives you a list of your variables
foreach varname in varlist qqq - zzz {
* this says to generate lagged variables for all variables in the
* variable list between qqq and zzz
  forvalues i=1/9 {
  *generate 9 lagged values for each
     by date, sort: gen lag`i'`varname'=`varname'[_n-`i']

so, if you have variables 10 varaibles between qqq and zzz inclusive, this script will generate 9 lagged variables for each.

October 2, 2008

UCLA stata graphing

Filed under: Data Insights, Stata — Tags: , , — howardchong @ 10:57 pm


Just want to give a shout out that the people at UCLA statistical consulting rock my world. They give me lots of understanding about how to get graphs to look they way I want them to.

stata transpose string variable without xpose

Filed under: coding, Data Insights, Stata — Tags: , , , , — howardchong @ 10:52 pm

So STATA will let you transpose the data with the xpose command, but this does not handle string data.



I had a set of stock price series. Variable names were data and the stock codes. rows were days



1/1/2005   $1    $5   $10


12/31/2005 ...


So, I managed to do it as follows:

1) First, rename all stock variables “price”+STOCKNAME

foreach vn of varlist STOCK1-STOCKN {
  quiet: rename `vn’ price`vn’

2) reshape long
3) reshape wide


reshape long price, i(realdate) j(name) string
drop date
reshape wide price, i(name) j(realdate)


Note that I needed realdate to be an integer, so I ran a
gen realdate=date(datestr,”mdy”)
and then dropped date.
If I keep date as a string, I can’t have the slashes in the string variable name, so you do have to somehow convert it to something you want. You can replace the slashes with underscores and then add the “string” argument to the second reshape.

October 1, 2008

stata, double quotes, file names with spaces, foreach

Filed under: Data Insights, Stata — Tags: , , , , , , , — howardchong @ 9:59 pm

It took me more than 10 minutes to figure out, so I’m posting this tip here.

I needed to loop over several files. Pseudocode would look like this:

global files a.csv b.csv c.csv

foreach file of global files {



Problem was that my filenames had spaces. I tried the following, but it didn’t quite work.

global files “a data.csv” “b data.csv” “c data.csv”


The solution that finally worked was to use Stata’s annoying double quotes:


global filelist `””STOXX 600 1of3.csv” “STOXX 600 2of3.csv” “STOXX 600 3of3.csv””‘


Note the extra `” and “‘ at the ends.

Blog at