Setting Up a Connection to Local Data

Use the instructions in this section to create a Data Source Name (DSN) for accessing data on the local workstation. When you access local data from an ODBC-compliant application, the SAS ODBC Driver starts a SAS ODBC server, unless a SAS ODBC server is already running. The SAS ODBC Driver then connects to the SAS ODBC server and provides access to the libraries that are associated with the DSN.

Access the SAS ODBC Driver Configuration dialog box.
Click the Servers tab. In the Name field, enter a name such as local. This value identifies the name of the SAS ODBC server process. Local (Single User) appears in the SAS Server Type field.
Click Configure. The Local Options dialog box appears.

Each field in the Local Options dialog box contains default values that you can change by typing over them.

Path

specifies the fully qualified pathname for the SAS executable file (SAS.EXE) that you use to start a SAS session. The default path is C:\Program Files\SAS\SASFoundation\9.2\sas.exe(footnote1). If this field is left blank, then no attempt is made to start a SAS ODBC server when you connect to your data source.

Working Directory

specifies the fully qualified pathname for the directory that you want to use as the SAS working directory. This directory is usually where your SAS program files and documents are located. The default path is C:\Program Files\SAS\SASFoundation\9.2.

Startup Parameters

specifies the parameters that are used to invoke SAS. The default values are initialization statement (-initstmt), which executes a SAS macro (%SASODBC), which invokes the SAS ODBC server. The local value is only an example. It is a SAS macro parameter whose value is taken from the name that you specified in the Name field of the Servers tab. The -icon option specifies that the SAS ODBC server should be invoked as a minimized SAS session because no interaction with the server is required. The -nosplash option (not shown) specifies that the SAS session is invoked without displaying the SAS logo and copyright information.

The %SASODBC macro is provided with SAS and is found in !SASROOT\core\sasmacros\sasodbc.sas(footnote2). The sasodbc.sas file executes PROC ODBCSERV.

The sasodbc.sas file can be modified to add SAS options or SAS statements, such as the LIBNAME statements mentioned in Defining Libraries at Server Start-Up Time. In addition, you can add options to PROC ODBCSERV. The available options are the same as those for PROC SERVER. For more information, see SAS/SHARE User's Guide.

An additional option, LOG=QUERY, is relevant for servers that are used by the SAS ODBC Driver. This option causes the server to log SQL queries. (By default, the server logs update and output operations, but not queries.) This option is useful when you need to see the queries that the server receives from an ODBC client application.

If your SAS session is installed on a network drive and is shared by multiple users, then you probably do not want individual users to modify the sasodbc.sas file. Instead, a user can make a copy of the file and store it in a personal library. In this case, the user must add the -sasautos option either to the Startup Parameters field or to the local config.sas file to indicate the pathname for the library, as in the following example: -sasautos c:\programs\sas

For more information about SAS system options and SAS statements, see SAS Companion for Windows.

Timeout

specifies, in seconds, how long to wait for the SAS ODBC server to start and to register itself. The default is 60 seconds.
Click OK to return to the Servers tab.

Important! Click Add to save the server definition.
Click the Libraries tab. Define a library for each data library that you want to access with this DSN.

Name

enter a name for an existing physical SAS library that you want to access. (If you are familiar with SAS, this field corresponds to the libref in the SAS LIBNAME statement.) The name can be up to eight characters. The first character must be a letter or an underscore. Subsequent characters can be letters, numeric digits, or underscores. Blank spaces and special characters are not allowed. For example, you might use the name cost to designate a library of cost accounting data. The SAS library can include SAS data files, SAS data views, or both.

Note: If you use an ODBC application that exports databases using one-level names, then you need to define a library called user.

Host File

enter the physical name of the library. This must be a valid pathname for the local workstation. For example, c:\data\costacct and \\acctsrv\customers are valid pathnames.

Description

provide a description of the library to remind yourself or other users what the library contains. Providing this value is optional.

Engine

enter the name of the SAS engine that is required for writing to and reading from this library. This setting is necessary only if you do not want to use the V9 engine that is the default for SAS 9.2. For information about other engines that might be available, see the description of the LIBNAME statement in the SAS Companion for Windows. Providing this value is optional.

Options

enter options for the library that you are defining, such as ACCESS=READONLY.
Click Add to save your library information. The library name is added to the list of libraries on the left.
Click the General tab.
Provide a name in the Data Source Name field. Use the Server menu to select the correct server for the DSN. For more information about SQL options, see SQL Options on the General Tab.
Create an entry in the services file to associate the server with a TCP/IP network port. The service name must match the server name that was entered on the Servers tab:
```
local    6090/tcp  # service name for local access to SAS data
```
For more information about creating entries, see TCP/IP Services File.

FOOTNOTE 1:The SAS ODBC Driver queries the registry for the current version of SAS and the value of DefaultRoot. This value is concatenated with \sas.exe to create the default path. The default working directory is the value of DefaultRoot. [return]

FOOTNOTE 2:!SASROOT is a logical name for the directory in which you install SAS. For more information, see SAS Companion for Windows.[return]