Incorporating SQL in Stata code

Here is a snippet of code showing how one can include SQL in Stata code (I assume that the ODBC connection has been set up).

#delimit ;

local sql "
 FROM director_bio.directorship_results
 WHERE non_match AND start_date < date_filed
 ORDER BY random()
 LIMIT 50";

odbc load, exec("`sql'") dsn("iangow") clear;
Posted in PostgreSQL, Stata, Uncategorized | Tagged , , | Leave a comment

Playing with times in Python

from datetime import datetime
from pytz import timezone, utc

pacific = timezone('US/Pacific')

gemma_bd = datetime(2007, 8, 27, 7, 45)
gemma_bd = pacific.localize(gemma_bd, is_dst=True)

print(utc.localize(datetime.utcnow()) - gemma_bd)
Posted in Uncategorized | Leave a comment

Installing PostgreSQL 9.6 using MacPorts

In another post, I describe how to install PostgreSQL with PL/R. The purpose of this post is to provide a streamlined version without PL/R.

1. Install PostgreSQL using MacPorts..
Of course, I assume you’ve got MacPorts up and running on your system.

sudo port install postgresql96 +perl +python27
sudo port install postgresql96-server

2. Set up PostgreSQL

I first need to initialize the database cluster and then get the server running. The following comes straight from the on-screen instructions supplied with the MacPorts port postgresql96-server. I wonder if it would work if I used the existing (and hidden) _postgres user. I also wonder if I should use the --encoding=UTF8 --locale=en_US options I used in my earlier installation.

sudo mkdir -p /opt/local/var/db/postgresql96/defaultdb
sudo chown postgres:postgres /opt/local/var/db/postgresql96/defaultdb
sudo su postgres -c '/opt/local/lib/postgresql96/bin/initdb -D /opt/local/var/db/postgresql96/defaultdb'

Sometimes permissions are messed up (and PostgreSQL will complain). To fix these:

sudo chmod -R 755 /opt/local/var/db/

Note that MacPorts creates a launch daemon. To load it now and to make sure it launches on system start, do:

sudo defaults write /Library/LaunchDaemons/org.macports.postgresql96-server.plist Disabled -bool false
sudo launchctl load /Library/LaunchDaemons/org.macports.postgresql96-server.plist

Then do some set-up to get the database going.

sudo su - postgres
/opt/local/lib/postgresql96/bin/psql -U postgres -d template1

I tend to do my work using a database named “crsp” (originally I just had CRSP data in there) under my own user name. So I set stuff up accordingly. Within psql:


4. Figure out how to get data into the database.

Posted in Uncategorized | Leave a comment

Executing SQL in a file against an ODBC source from Stata

I recently discovered that Stata has good support for OBDC. While the built-in functionality includes odbc sqlfile, a “batch job alternative to odbc exec,” there seems to be no equivalent alternative for odbc load (even though odbc load is surely the more useful function).

But this is easily addressed using an .ado program I adapted from that discussed in an earlier post.

I created a file (loadsql2.ado) containing the following:

program define loadsql2
*! Load the output of an SQL file into Stata, version 1.6 (
version 13.1
syntax using/, DSN(string)
tempname sqlfile exec line;
file open `sqlfile' using `"`using'"', read text;
file read `sqlfile' `line';
while r(eof)==0 {;
    local `exec' = "``exec''
    file read `sqlfile' `line';
file close `sqlfile';
* display "``exec''";
odbc load , exec("``exec''") dsn("`dsn'") clear;

I placed this file in ~/Library/Application\ Support/Stata/ado/personal.

Since I have a ODBC DSN iangow set up, I can now issue the following (see my earlier post for details on ~/some_sql.sql):

loadsql2 using "~/some_sql.sql", dsn("iangow") 

and then I have data in Stata:

. list in 1/5

     |  gvkey    datadate       at       ni |
  1. | 001073   31dec1985    3.095     .541 |
  2. | 001073   31dec1986    4.422     .584 |
  3. | 001073   31dec1987      3.9    -.359 |
  4. | 001074   31dec1970   34.856    1.716 |
  5. | 001073   31dec1988    2.067   -1.191 |
Posted in Mac, PostgreSQL, Stata | Leave a comment

Another way to access PostgreSQL from Stata

In an earlier post, I discussed a way of accessing PostgreSQL from Stata. One downside is that this approach would not work for Windows users without significant modification. I asked a question on StackOverflow to see if anyone could identify the necessary modifications.

While one user could answer my StackOverflow question, it seems the easier way to access PostgreSQL from Stata on Windows is via ODBC. It turns out to be fairly straightforward to get ODBC working on Mac OS X (see here).

Posted in Uncategorized | Leave a comment

GitHub code repository

For anyone reading this blog, I also have a repository on GitHub dedicated to code for compiling data from standard sources (e.g., WRDS). I’m still in the process of documenting this, but the documentation for some parts (e.g., Perl scripts for getting data from WRDS into PostgreSQL) is fairly complete.

Posted in Uncategorized | Tagged , | Leave a comment

Installing R on Mac OS from the command line

I just updated R remotely. I wanted to avoid the hassle of logging in via VNC (“Screen Sharing”), so I did this:

sudo installer -pkg R-3.2.3.pkg -target /
rm R-3.2.3.pkg

Now, to get RStudio from the command-line:

sudo hdiutil attach RStudio-0.99.489.dmg
sudo cp -R /Volumes/RStudio-0.99.489/ /Applications/
sudo hdiutil detach /Volumes/RStudio-0.99.489
rm RStudio-0.99.489.dmg
Posted in Mac, R | Tagged , | Leave a comment