Pulling data into Stata from PostgreSQL in Mac OS X

The following is perhaps of interest to Stata users.

This was pretty easy to install on my (OS X) laptop (a few tweaks to the Makefile, then the usual “make; sudo make install”). I suspect one could get it to work on Windows.

I found pgload here and made loadsql myself (a stretch for my limited Stata skills, but it was mostly copy-and-paste from Google). Here localhost is my laptop, while some.url is the computer in my attic.

  ___  ____  ____  ____  ____ (R)
 /__    /   ____/   /   ____/
___/   /   /___/   /   /___/   13.1   Copyright 1985-2013 StataCorp LP
  Statistics/Data Analysis            StataCorp
                                      4905 Lakeway Drive
     MP - Parallel Edition            College Station, Texas 77845 USA
                                      800-STATA-PC        http://www.stata.com
                                      979-696-4600        stata@stata.com
                                      979-696-4601 (fax)
20-user 8-core Stata network license expires 17 Nov 2014:

. pgload "dbname=crsp host=localhost" "SELECT permno, date, abs(prc) AS prc FROM crsp.dsf LIMIT 10"
. list
     +-----------------------------+
     | permno        date      prc |
     |-----------------------------|
  1. |  10147   08aug1994   14.375 |
  2. |  10147   09aug1994    14.75 |
  3. |  10147   10aug1994     15.5 |
  4. |  10147   11aug1994    15.25 |
  5. |  10147   12aug1994     15.5 |
     |-----------------------------|
  6. |  10147   15aug1994    15.75 |
  7. |  10147   16aug1994   16.375 |
  8. |  10147   17aug1994   16.375 |
  9. |  10147   18aug1994   16.125 |
 10. |  10147   19aug1994   15.625 |
     +-----------------------------+
. clear
. pgload "dbname=crsp host=some.url" "SELECT permno, date, abs(prc) AS prc FROM crsp.dsf LIMIT 10"
. list
     +-----------------------------+
     | permno        date      prc |
     |-----------------------------|
  1. |  10000   07jan1986   2.5625 |
  2. |  10000   08jan1986      2.5 |
  3. |  10000   09jan1986      2.5 |
  4. |  10000   10jan1986      2.5 |
  5. |  10000   13jan1986    2.625 |
     |-----------------------------|
  6. |  10000   14jan1986     2.75 |
  7. |  10000   15jan1986    2.875 |
  8. |  10000   16jan1986        3 |
  9. |  10000   17jan1986        3 |
 10. |  10000   20jan1986        3 |
     +-----------------------------+

. loadsql using "~/some_sql.sql", conn("dbname=crsp host=some.url") 
. list in 1/10
     +-------------------------------------+
     |  gvkey    datadate       at      ni |
     |-------------------------------------|
  1. | 001000   31dec1961        .       . |
  2. | 001000   31dec1962        .       . |
  3. | 001000   31dec1963        .    .003 |
  4. | 001000   31dec1964    1.416    .052 |
  5. | 001000   31dec1965     2.31   -.197 |
     |-------------------------------------|
  6. | 001000   31dec1966     2.43    .164 |
  7. | 001000   31dec1967    2.456    -.09 |
  8. | 001000   31dec1968    5.922    .463 |
  9. | 001001   31dec1982     8.59    .965 |
 10. | 001000   31dec1969   28.712   1.766 |
     +-------------------------------------+

Here’s the SQL in ~/some_sql.sql:

SELECT gvkey, datadate, at, ni
FROM comp.funda
LIMIT 1000;

Installation

Here’s how I did this. Note that I have PostgreSQL installed from MacPorts and I also have wget (sudo port install wget).

1. Get file from Stata and Oxford site

cd ~/Downloads
wget http://code.ceu.ox.ac.uk/stata/pgload-0.1.tar.gz
tar -zxvf pgload-0.1.tar.gz
mkdir ~/Downloads/stata
cd ~/Downloads/stata/
wget http://www.stata.com/plugins/stplugin.c
wget http://www.stata.com/plugins/stplugin.h

2. Edit Makefile

vim ~/Downloads/pgload-0.1/Makefile 

The last step opens up the Makefile. I make changes to five lines (the last two will differ if you installed PostgreSQL 9.3 from somewhere other than MacPorts).

PLUGIN_SYS=APPLEMAC
INSTALL_LOCATION=~/Library/Application\ Support/Stata/ado/personal
STATAPLUG_INC=~/Downloads/stata
PQ_INC=/opt/local/include/postgresql93
PG_SERVER_INC=/opt/local/include/postgresql93/server

I then save and exit. Then

3. Compile and install

cd ~/Downloads/pgload-0.1
make
sudo make install

4. Add loadsql.ado file
The loadsql.ado file is placed in ~/Library/Application\ Support/Stata/ado/personal and contains the following:

program define loadsql
*! Load the output of an SQL file into Stata, version 1.4 (iandgow@gmail.com)
version 13.1
syntax using/, CONN(string)
 
#delimit;
tempname sqlfile exec line;
 
file open `sqlfile' using `"`using'"', read text;
file read `sqlfile' `line';
 
while r(eof)==0 {;
    local `exec' `"``exec'' ``line'' 
     "';
    file read `sqlfile' `line';
};
 
file close `sqlfile';
 
* display "`conn'";
 
pgload "`conn'" "``exec''", clear;
* pgload "``dsn''" "SELECT permno, date, abs(prc) AS prc FROM crsp.dsf LIMIT 10", clear;
 
end;

This is based on some code I found for MySQL (can’t remember where) and tweaked.

Advertisements
This entry was posted in Mac, PostgreSQL, Stata. Bookmark the permalink.

3 Responses to Pulling data into Stata from PostgreSQL in Mac OS X

  1. Pingback: What do I need to compile a Stata plugin for PostgreSQL on Windows? - BlogoSfera

  2. Pingback: Another way to access PostgreSQL from Stata | iangow

  3. Pingback: Executing SQL in a file against an ODBC source from Stata | iangow

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