Setting Up an ODBC Data Source

Once the ODBC drivers are installed on your workstation (each software package handles the installation process differently; consult your documentation for more information) you may have to create an ODBC data source which serves as a configuration profile for the data files. You may not have to create a data source, however, if the data files reside locally on your workstation hard disk and the client application software already created a generic data source. To check which data sources have already been created, use the ODBC driver manager. There is an icon for the ODBC Administrator located in the Control Panel under Windows 98/ME/NT/2000/XP. Other versions of Windows may or may not already have an icon for the ODBC Administrator, so a certain amount of searching may be necessary to find it. If you cannot find an icon for ODBC Administrator, check the C:\WINDOWS\SYSTEM subdirectory for the file ODBCAD.EXE (the 16-bit ODBC driver manager) or the C:\WINDOWS\SYSTEM32 subdirectory for ODBCAD32.EXE (32-bit version).

To review, modify or add ODBC data sources, run the ODBC Administrator program (also referred to as ODBC driver manger) by either double-clicking on the appropriate icon or using the Start->Run option in Windows. The dialogue box will look something like this:

In the text box labeled User Data Sources, you will find a list of all the preconfigured ODBC data sources. Again, while some of these data sources were installed along with their respective packages and no user configuration was required, some of these sources have to be added manually. If you are going to use ODBC primarily as a medium for the translation of files from one format to another, and once you install the ODBC drivers the data sources are created automatically, then you may never have to use the ODBC administrator. For example, all of the Microsoft-related data sources listed in the above dialogue box (e.g. dBASE, Excel, FoxPro, etc.) were automatically installed with Microsoft Office, so no further configuration is necessary for these data sources.

However, not all data sources are defined automatically. For example, suppose we wanted to see what ODBC data drivers are installed on a workstation. Because ODBC driver installation is independent of ODBC data source configuration, we would have to click the Drivers tab in the ODBC administrator to see a list of the installed drivers. The following dialogue box would then appear:

Use the scroll bar to view the entire list of drivers installed. If you compare these two examples, you can see that there are two ODBC drivers installed for which there are no corresponding data sources (SAS and SQL Server). The Drivers dialogue box is used only to display existing ODBC.

The next section contains a few examples of how you can create a data source. Unfortunately, it is impossible to explain in greater detail how data sources are added and configured in this discussion because the process is unique for each ODBC driver. You should consult your host application software manual for more information on how to install the ODBC driver and create a ODBC data source.

Creating an ODBC Data Source

Generally speaking, there are two types of ODBC data sources -- what one could refer to as "generic" and "file-specific" data sources. A generic data source is automatically created by some programs during the installation stage. These types of data sources, such as the ones installed by Microsoft Office, provide all of the information necessary to access a file using ODBC, except for the name and location of the file. Thus, when using a generic data source, the user must specify the drive, path, and filename interactively.

In contrast, a file-specific data source is a much more precise configuration that designates a particular file in a particular location as an ODBC data source. Creating a file-specific data source speeds up the process of accessing a file because it requires less user intervention when opening ODBC data files. A file-specific ODBC data source is usually created in situations where a single file is going to be accessed many times because it allows you to refer to the file by the assigned name rather than having to select the file each time you wish to access it.

Below are three examples of creating file-specific ODBC data sources to help illustrate how you can use this feature. The examples include creating file-specific ODBC data sources using the ODBC drivers installed by Microsoft Office and SAS.

Creating an ODBC Data Source with Excel

Microsoft Excel is a multidimensional spreadsheet program with each file containing a "workbook" composed of one or more "worksheets." In each worksheet the data are organized by columns and rows, with the columns representing the variables, and the rows representing observations (a particular column/row combination is referred to as a "cell"). When using Excel, you have the option of using the generic data source that is supplied by Microsoft which requires you to supply the filename and location of the data file when you use ODBC to import the file into SPSS or SAS. Or you can also create a file-specific data source to simplify the task.

Regardless of whether you are using the generic ODBC data source or creating a file-specific data source, you must first assign a name to the data in Excel before you can import the data into SAS or SPSS using ODBC. Most casual users are not aware of the use of names in Excel. This feature allows you to assign a particular label to a formula or range of cells as a shortcut when referring to that object. ODBC can access only data that are labeled in this fashion. So the first step you must do to read these data using ODBC is to assign a name to the data you wish to import.

Imagine that you have an Excel spreadsheet called temp.xls located in the c:\work subdirectory. To prepare this file:

1.      Open this file in Excel by choosing File->Open, typing in the location and filename (e.g. c:\work\temp.xls), and then click OK.

2.      Highlight (click-and-drag) the data you wish to read using ODBC. Note: do not use the Edit->Select all shortcut to highlight the data. When you are making your selection in Excel, you should highlight only the data you wish to export, and be sure you do not include any extraneous empty columns or rows. After selecting a range of data, you must assign a name to the selection.

3.      From the main menu, select Insert->Name->Define.

4.      Type data in the space at the top of the dialogue box as the name of the selected data, and then click OK. You can define as many different selections as you like. For example you could have one name assigned to the entire file, and another to a subset of the data. In this example, however, we will only have the one selection named data.

5.      Finally, save the spreadsheet using File->Save and exit Excel using File->Exit. You are now ready to read these data using ODBC.

At this point we could just use the generic ODBC data source to read this file, but let us automate the process a bit more by creating a file-specific data source. Do the following:

6.      Launch the ODBC Administrator program.

7.      When the Data Sources dialogue box appears, click the Add... button.

8.      From the list of available ODBC drivers that then appears, click once on Microsoft Excel Driver (*.xls) and then click Finish.

The following dialogue box will appear:

Now you must assign a data source name. The name can be anything you want but it is always best to make the name something short and easy to remember, for you will be using this name in SAS and SPSS as a reference to this data source. For example, type temp_xls as the data source name. You may also include a longer description in the text box immediately below.

9.      Next, click the Select Workbook... button to select the specific Excel file that you wish to associate with this data source name. You may use the files, directories, drives boxes and the mouse to browse through your hard disk to find the file, or simply type the drive, path and file name with extension in the Database Name text box in the upper left hand corner. In this case, you would type c:\work\temp.xls as the drive, path and filename, and then click OK.

10.  Finally, click OK once more and you will return to the Data Sources dialogue box. If you examine the list of User Data Sources, you should now see a listing for the file temp_xls.

11.  We are now finished configuring this data source, so click OK to exit the ODBC Administrator program.

You are now ready to use this data file with other ODBC applications.

Creating an ODBC Source with Access

Microsoft Access is a relational database program with each database file containing one or more tables of data. The data are organized in columns and rows, with the columns representing fields (variables) and the rows representing records (observations). With ODBC, you can read Access database or query tables. As with Microsoft Excel, you are able to use either the generic ODBC data source, or you can create a file-specific data source to simplify the task of transferring data.

The process for creating a file-specific data source is nearly identical to the one for Microsoft Excel. For example, let us say you have an Access database called temp.mdb located in the C:\WORK subdirectory, and you wished to create a file-specific data source. You would have to do the following:

1.      Launch the ODBC Administrator program.

2.      When the Data Sources dialogue box appears, click the Add... button.

3.      From the list of available ODBC drivers that then appears, click once on Microsoft Access Driver (*.mdb) and then click Finish.

The following dialogue box will appear:

Now you must assign a data source name. The name can be anything you want but it is always best to make the name something short and easy to remember, for using this name in SAS and SPSS as a reference to this data source. For example, type temp_mdb as the data source name. You may also include a longer description in the text box immediately below.

4.      Now you must specify the file that is going to be associated with this data source. Click the Select... button to assign a filename to this data source.

5.      When the Select Database dialogue box appears, you may use your mouse to search the hard disk and subdirectories to find the file, or you may just type in the filename in the text box in the upper left hand corner. In this example, you would type C:\WORK\TEMP.MDB. Click OK to return to the dialogue box shown above.

6.      Click OK to return to the Data Sources dialogue box. You will now see your new data source in the list of User Data Sources.

7.      We are now finished configuring this data source, so click OK to exit the ODBC Administrator program.

You are now ready to use this Access database with other ODBC applications.

Creating an ODBC Data Source with SAS

This example assumes that you have already installed the SAS ODBC drivers (for more information on ODBC and SAS, consult SAS ODBC Driver: User's Guide and Programmer's Reference) at http://www.indiana.edu/~statmath/stat/sas/sashtml/onldoc.htm. Imagine that you wanted to be able to read a particular SAS data set located on your hard disk into another application (e.g. SPSS). The name of the SAS data set in this example is ODBCTEST.SAS7BDAT and it is located in the C:\WORK subdirectory. To do this, you must first define an ODBC SAS data source. Unlike the Microsoft Office ODBC drivers installation, SAS does not create a generic ODBC data source, so you must create a data source for each SAS data set you wish to access. To create a SAS data source, you would have to do the following:

1.      Launch the ODBC Administrator.

2.      Add a new SAS data source by clicking the Add button.

You will then see something similar to the following dialogue box:

3.      Select SAS in the Installed ODBC Drivers list and then click Finish.

You will then see the SAS ODBC Driver Configuration dialogue box:

4.      Your next step is to assign a data source name. This can be anything but you should use something that you will be able to remember later. In this example, click once in the box labeled Data Source Name, and type in ODBCTEST as the data source name (this is the name of the SAS data set). The Description field is optional and it allows you to assign a full description to this data source.

5.      The next step is to establish the location of the ODBC server. While SAS has the ability to serve as a remote ODBC data server, this requires special software that is not generally available at IUB (i.e. this software does not come with the standard SAS installation and must be ordered separately), therefore the "server" you will define in this example is your own workstation. Click on the tab marked Servers. Next, in the box labeled  Name, type in the name you wish to assign to your local workstation. In this example, type local in this box.

6.      Click the CONFIGURE button. A dialogue box titled Local Options will then appear.

7.      In the Local Options box you must specify the correct location of SAS on your hard disk, but the rest of the settings can be left at the defaults. So, for example, if SAS is located on the C: hard drive, the SAS Path box should read c:\Program Files\SAS  Institute\ SAS\V8\sas.exe. Once you are satisfied with the configuration, click OK. Make sure that Local (Single User) in the SAS Server Type box. 

8.      Click <<Add<< to store these settings. It should be noted that you will only need to assign the local workstation as a server once. Any new SAS data sources defined will automatically use the same server (i.e. your local workstation).

9.      Next, you must establish a library reference, much as if you were running SAS without ODBC. Click on the Libraries tab and then fill in the Name and Host File boxes. In the box labeled Name, type in an alias for the subdirectory where your data is located. For the purposes of this example, you would type ODBCDATA in this box (names can be up to eight characters and can contain letters and numbers but must begin with a letter).

10.  In the box labeled Host File, type the actual name of the subdirectory where the data is located. In our example, you would type C:\WORK in the Host File box. Click <<Add<< to store these settings.

11.  When you are done, click OK to return to the User Data Sources box. You will now see a new data source in the list called ODBCTEST. This data source will allow other ODBC client applications to access the SAS data set called ODBCTEST in the C:\WORK subdirectory. To change the configuration of this data source, click once on ODBCTEST and then click Configure.

12.  We are now finished configuring this data source, so click OK to exit the ODBC Administrator program.

These examples illustrate the various types of steps that you might have to go through to access a data set via ODBC. Because the data source configuration is dependent on the type of application used and the location of the data (i.e. local versus remote), you may have to spend quite a bit of time setting up the data sources properly. Once that step is completed, however, you will then be able to transparently move data back and forth between ODBC compliant applications.