Using STATA to access library data, like SAS

[Luis Amaya]

Dear everyone,

I hope you are fine. I started working in a financial institution a few months ago, and I am doing some data analysis. Because my SAS license has been delayed, I have been working with Stata, and learning some R, and I rely on the actuary of my area to give me the databases to do some of my tasks.

He is not familiar with Stata, so he does not know how Stata's capabilities. He told me that I should try to "directly connect to the server". He does this with SAS. I was wondering if I could so something similar with Stata.

Here is the command he is using in SAS:
Libname LAC ORACLE user='XXXXXX' password='XXXXXX' path='XXXX_exadata' schema='GORAPR';

Is there a way to do this in Stata?

I saw the command
odbc query

but I am not sure how to input the stuff.

Thank you very much.

[William Lisowski]

As a former Windows user who has accessed Oracle data on database servers from desktop software, I'll hazard a guess, but please don't take this as authoritative in any way.

I think you are going to need to seek help from your computing support group to configure an ODBC source in your Windows system settings to access the Oracle database using the credentials you were given. Once this is done, reading the documentation disgorged by Stata's help odbc should point you in the right direction. The user and password you were given correspond to connect_options of the same name. It appears that the path and schema you were given will be needed to set up the ODBC source. If you're daring, you might find do-it-yourself help for this task through Google, but there can often be technical constraints introduced by the computing environment.

Actually, you can start by trying Stata's odbc list to see if perhaps there already is a suggestively-named ODBC source configured on your Windows system.

My bottom line is that I think the first step is to set up the ODBC source, which is part of Windows, not Stata, and so will require non-Stata support familiar with your computing environment.

[ben earnhart]

And, (possibly depending on what version of Oracle) you will likely need to install and configure the proper Oracle client before you can even set up a DSN (ODBC source). Your tech support people are probably more familiar with configuring MS Access than arbitrary stats packages, so let them know that you need a DSN set up, like for Access, that you intend to use with Stata. If you tell them you want to connect Stata, they may just tell you to go away. But if you use the magic words "DSN, like with Access," they're more likely to be willing to talk and at least try. Might still not be possible, but getting them to even try is obviously a necessary first step.

[Luis Amaya]

Thank you. I thought it was going to be easier, given how easy the actuary told me it was.

[ben earnhart]

SAS has plugins for accessing just about any kind of data source. If you've ever installed it from scratch, there are well over 100 different steps, about half of which are database connectors. It has a rich history of being used in enterprise environments, with 100s of people working from the same data in real-time from numerous sources. Stata on the other hand is more for the solitary analyst working with one data source at a time, and that it can do ODBC at all is I think a fairly recent development. With SAS, it connects directly, but with Stata, it needs the operating system to dumb it down to a common standard, ODBC (which you connect to with a DSN).