Working with Wharton Analysis Information Companies (WRDS) information in Stata is now even simpler. I beforehand wrote about accessing WRDS information through ODBC. With Stata 17, utilizing JDBC makes configuring WRDS and Stata even simpler—and the steps to configure are the identical throughout all working programs. Whether or not you obtain WRDS information to your native machine or work within the cloud, the command to make use of in Stata for JDBC is jdbc.
To arrange jdbc, you might want to obtain a JDBC driver .jar file. To get the right .jar file, go to your database vendor’s web site. The WRDS platform makes use of a Postgres database, so obtain the driving force from https://jdbc.postgresql.org. This .jar file will work with jdbc on Unix, Mac, or Home windows so long as you save the file alongside Stata’s ado-path. I moved the downloaded .jar file to the private ado-path listing on my Home windows machine, which is
C:Userskevinadopersonal/
For my Mac, it’s
/Customers/kevin/Paperwork/Stata/ado/private
and for Unix, it’s
/house/kevin/ado/private
Whether or not working within the WRDS cloud or regionally in your machine, additionally, you will want the right Java driver class title, URL, username, and password. For WRDS, your connection setting would be the similar as mine, aside from the username and password, which WRDS will provide. The settings are
| JDBC Settings | WRDS Settings |
|---|---|
| Driver File | postgresql-42.3.0.jar |
| Java Class Identify | org.postgresql.Drive |
| URL | jdbc:postgresql://wrds-pgdata.wharton.upenn.edu:9737 |
| Username | stata |
| Password | secret |
Notice that the everyday URL syntax for JDBC is
jdbc:DBVenderName://IP_or_URL:Port/DBName?ConnectionOptions
As a result of these settings are difficult to recollect, it’s greatest to create a do-file that shops them. My do-file is
native jar "postgresql-42.3.0.jar" native classname "org.postgresql.Driver" native url "jdbc:postgresql://wrds-pgdata.wharton.upenn.edu:9737/wrds?ssl=require&sslfactory=org.postgresql.ssl.NonValidatingFactory" native person "stata" native go "secret"
To retailer these settings for the present Stata session, add the next jdbc join command to your do-file, and run the do-file.
jdbc join, jar("`jar'") driverclass("`classname'") url("`url'") ///
person("`person'") password("`go'")
jdbc remembers your final settings for your entire Stata session. In order for you these saved between classes, you possibly can add them to a profile.do file.
To checklist all of the tables within the database, sort jdbc showtables.
Sometimes, the checklist displayed from this command is a number of hundred traces lengthy. The best way to seek for particular tables in WRDS is thru utilizing SQL wildcards. For instance, the % wildcard matches zero or extra characters.
. jdbc showtables dj_equities_201% Database: wrds ---------------------------------------------------------------------------- Tables ---------------------------------------------------------------------------- dj_equities_2010 dj_equities_2011 dj_equities_2012 dj_equities_2013 dj_equities_2014 dj_equities_2015 dj_equities_2016 dj_equities_2017 dj_equities_2018 dj_equities_2019
If the title of the desk, you possibly can load it by typing jdbc load, desk(“djdaily”).
If a desk is massive, it’s greatest to load the database columns you might want to analyze fairly than your entire desk. Doing this protects reminiscence. To checklist the columns of a desk, use jdbc describe.
. jdbc describe djdaily (28073 observations loaded) Desk: djdaily ---------------------------------------------------------------------------- Column title Column sort ---------------------------------------------------------------------------- date date djc float8 djct float8 dji float8 djit float8 djt float8 djtt float8 dju float8 djut float8 date date djc float8 djct float8 dji float8 djit float8 djt float8 djtt float8 dju float8 djut float8
You possibly can load particular columns of a desk utilizing a SELECT SQL assertion.
. jdbc load, exec("SELECT date, djc, dji, djit FROM djdaily")
(28073 observations loaded)
Right here I’ve proven you just a few methods you possibly can work with WRDS information utilizing Stata 17’s new jdbc command. If you’re working in one other cloud atmosphere or utilizing a unique database vendor, the steps to configure JDBC and Stata must be comparable. As well as, jdbc has many different options I’ve not demonstrated. jdbc has different subcommands to assist handle your database connections, insert information, and execute SQL statements. You possibly can examine these subcommands within the [D] jdbc entry within the Stata Information Administration Reference Guide.
