Setting Up the Driver¶

Once you are in your SAFE desktop, you will need to add an odbc driver by following the steps below. This will allow stata to connect to the database.

Step One: Open ODBC Data Sources¶

After you have launched SAFE and it has loaded up, click on the search bar and type "odbc" into it. You should see "ODBC Data Sources" appear. Click on it to launch the window. You will have to enter your JHED ID and password to proceed.

Step Two: Add New Driver¶

In the window which opens up, click on "Add" in the top right

Step Three: Select Driver Type¶

A list of driver * will appear. Select "ODBC Driver 17 for SQL Server." Then click "Finish" at the bottom.

Step Four: Enter Database Information¶

In the "Name" box, enter the name of the database you will be accessing.

In the "Description" box, enter a description of your data.

In the "Server" box, enter "esmpmdbpr4.esm.johnshopkins.edu"

Then, click "Next"

Step Five: Authentication¶

Make sure to select the first option, "With Integrated Windows authentication"

Then click "Next"

Step Six: Select Driver Type¶

Click on the box next to "Change the default database to"

Then, click on the arrow on the right of the box, and select your database from the drop-down list.

Click "Next"

Step Seven: Finishing and Testing Connection¶

On the next page, click "Finish" at the bottom. Then, click "Test Data Source" in the window which opens up. After a few seconds, you should see that the test was completed successfully. At this point, you can close out of the application.

Using Stata¶

Now that the ODBC driver is set up, Stata can be used to connect with the database and load the data directly into stata. Stata can be found in the "SAFE Applications" folder on the desktop.

Step One: Connect to the Driver¶

Once you have stata open, type "odbc list" into the command box and press enter. You should see the driver you created (which should be the same name as the database you are accessing).

Then, you can click on the driver's name to connect to it. Once you do this, a list of tables in your database will appear, which will look something like this:

Step Two: Loading Data¶

If you want to load a table in its entirety, you can use the load command by typing:

odbc load, table(tableName)

For example, if you wanted to work with all the data in the meds table, you would type:

You can also execute SQL queries directly in stata using exec,

odbc load, exec("SQL Query")

For example, if you wanted to get the medication id and the dosage for all medications in the meds table with a dosage of 600 mg, you would type:

Note: remember to type "clear" in between load commands!

Once you have the data loaded in, you can perform stata commands on it as you usually would.

Step Three: Saving the Data¶

When you want to save your data, you can simply select File > Export > and then choose how you would like your data exported (excel sheet, csv, etc)

Connecting to a Database Through Stata¶

Table of Contents¶