Using ODBC with Stata
Rita Sousa
(
rcsousa@bportugal.pt
)
Rita Sousa
(
rcsousa@bportugal.pt
)
Bank of Portugal
Center for Mathematics and Applications
Summary
Introduction
ODBC Support
Configuring ODBC
XX Congresso da Sociedade Portuguesa de Estatística
Using ODBC with Stata @Rita Sousa
III Portuguese Stata Users Group Meeting
2
ODBC in Stata
Loading data
Practical Example
Useful Tips
Final Remarks
Introduction
Open DataBase Connectivity (ODBC) is a standardized set
of function calls that can be used to access data stored in
database management systems.
Stata's
odbc
command
us
to
load,
write,
and
view
XX Congresso da Sociedade Portuguesa de Estatística
Using ODBC with Stata @Rita Sousa
III Portuguese Stata Users Group Meeting
3
Stata's
odbc
command
us
to
load,
write,
and
view
data from ODBC sources.
In my presentation I want to show you how to deal with
large databases using the ODBC resources.
ODBC Support
Import data from any ODBC data source, such as Oracle,
SQL Server, Access, Excel, MySQL, and DB2.
Export data to new or existing ODBC tables
Execute
custom
SQL
commands
XX Congresso da Sociedade Portuguesa de Estatística
Using ODBC with Stata @Rita Sousa
III Portuguese Stata Users Group Meeting
4
Execute
custom
SQL
commands
Configuring ODBC for Windows
Before you start using the odbc command in Stata, you
must first set up a data source name (DSN) in the ODBC
Data Source Administrator.
1.
Select
Control
Panel
XX Congresso da Sociedade Portuguesa de Estatística
Using ODBC with Stata @Rita Sousa
III Portuguese Stata Users Group Meeting
5
1.
Select
Control
Panel
2. Select System and Security in the Control Panel
3. Next select Administrative Tools
4. Double-click on Data Sources (ODBC) to open the
ODBC Data Source Administrator
Configuring ODBC for Windows
Create
XX Congresso da Sociedade Portuguesa de Estatística
Using ODBC with Stata @Rita Sousa
III Portuguese Stata Users Group Meeting
6
List of User
Data Sources
Names (DSN)
Create
a New DSN
Configuring ODBC for Windows
Select the appropriate driver
from the list
For example, choose
Microsoft Access Driver
(*.mdb)
XX Congresso da Sociedade Portuguesa de Estatística
Using ODBC with Stata @Rita Sousa
III Portuguese Stata Users Group Meeting
7
Configuring ODBC for Windows
Select your MS Access file to be in
the Data Source test that we are
creating
After click OK
XX Congresso da Sociedade Portuguesa de Estatística
Using ODBC with Stata @Rita Sousa
III Portuguese Stata Users Group Meeting
8
Configuring ODBC for Windows
64-bit Windows ships with two different ODBC Data Source
Administrators, 64-bit and 32-bit
Make sure you are using the correct version according to
your
Stata
version
XX Congresso da Sociedade Portuguesa de Estatística
Using ODBC with Stata @Rita Sousa
III Portuguese Stata Users Group Meeting
9
your
Stata
version
Stata provides odbc as the Client interface
ODBC in Stata
odbc list produces a list of ODBC data source names to
which Stata can connect
XX Congresso da Sociedade Portuguesa de Estatística
Using ODBC with Stata @Rita Sousa
III Portuguese Stata Users Group Meeting
10
odbc query show a list of table names available from a
specified data source
odbc describe lists column names and types for each
table available
Loading Data
In Stata we can use some commands to import data stored
in formats different from .dta such as insheet, infix,
import excel, etc.
odbc
load
reads
an
ODBC
table
into
memory
.
XX Congresso da Sociedade Portuguesa de Estatística
Using ODBC with Stata @Rita Sousa
III Portuguese Stata Users Group Meeting
11
odbc
load
reads
an
ODBC
table
into
memory
.
You can load an ODBC table specified in the table() option
or load an ODBC table generated by an SQL Command
specified in the exec() option.
Loading Data
nuemp nemp turnover
580071 1 21667
581033
8
2171229
XX Congresso da Sociedade Portuguesa de Estatística
Using ODBC with Stata @Rita Sousa
III Portuguese Stata Users Group Meeting
12
• odbc load id=nuemp nemp=pemp turnover=vn in 1/5,
table("Table_Test") dsn("test")
• odbc load, exec(`"Select top 5 nuemp, pemp as pemp, vn as turnover
From Table_Test"’) dsn("test")
581033
8
2171229
581915 1 68694
581952 1 32515
582590 4
Practical Example
Suppose now you want to access data stored in a Sql
Server database.
You have to register your ODBC database with the ODBC
Data
Source
Administrator
:
XX Congresso da Sociedade Portuguesa de Estatística
Using ODBC with Stata @Rita Sousa
III Portuguese Stata Users Group Meeting
13
Data
Source
Administrator
:
1. Create a new Data Source
2. Choose Sql Server Driver
3. Specify the name of the server you want to connect
Practical Example
XX Congresso da Sociedade Portuguesa de Estatística
Using ODBC with Stata @Rita Sousa
III Portuguese Stata Users Group Meeting
14
Practical Example
XX Congresso da Sociedade Portuguesa de Estatística
Using ODBC with Stata @Rita Sousa
III Portuguese Stata Users Group Meeting
15
Creating the
connection to the
Data Source “SPAI”
Defining the
Query statement
Loading and
Saving Data
Practical Example
XX Congresso da Sociedade Portuguesa de Estatística
Using ODBC with Stata @Rita Sousa
III Portuguese Stata Users Group Meeting
16
Practical Example
Loading data from
a master table
with information
XX Congresso da Sociedade Portuguesa de Estatística
Using ODBC with Stata @Rita Sousa
III Portuguese Stata Users Group Meeting
17
with information
of enterprises
Merging information
from districts dataset
Practical Example
XX Congresso da Sociedade Portuguesa de Estatística
Using ODBC with Stata @Rita Sousa
III Portuguese Stata Users Group Meeting
18
Writing Data
odbc insert writes data from memory to an ODBC table.
The data can change an existing table or create a new
ODBC table.
XX Congresso da Sociedade Portuguesa de Estatística
Using ODBC with Stata @Rita Sousa
III Portuguese Stata Users Group Meeting
19
odbc insert, table("Table_Name") dsn("DSN") insert_options
create – create a simple ODBC table
overwrite – clear data and write the data in memory to the ODBC table
insert - default mode to append data in memory to the ODBC table
Some Useful Tips
Stata keeps its entire dataset in memory. Usually it is faster
but it can be a disadvantage when your dataset is very
large.
drop
any
variables
you
don't
need
for
your
analysis
XX Congresso da Sociedade Portuguesa de Estatística
Using ODBC with Stata @Rita Sousa
III Portuguese Stata Users Group Meeting
20
drop
any
variables
you
don't
need
for
your
analysis
use the same variable names when you are combining
datasets
Use compress to optimize variables format
Encode strings
Use the cycles foreach or forvalues to do repeated
commands
Use preserve and restore to make temporary changes
to datasets
Final Remarks
Stata command odbc allows Stata to load, write, or view
data from ODBC sources.
This command offers a usefu set of function calls that can
be
used
to
access
data
stored
in
many
different
types
of
XX Congresso da Sociedade Portuguesa de Estatística
Using ODBC with Stata @Rita Sousa
III Portuguese Stata Users Group Meeting
21
be
used
to
access
data
stored
in
many
different
types
of
database management systems.
Oracle, SQL Server, Access, Excel, MySQL and DB2 are
some examples of available ODBC data sources.
Stata’s odbc is a resourceful solution that allow us to query
external databases and insert or update records in those
databases.
Using ODBC with Stata
Thank you for your attention
Using ODBC with Stata
Rita Sousa (rcsousa@bportugal.pt)
Bank of Portugal
Center for Mathematics and Applications