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 driver starts a SAS ODBC server, unless a SAS ODBC server is already running. The driver then connects to the SAS ODBC server and provides access to the libraries that are associated with the DSN.
Define the Server
  1. Access the SAS ODBC Driver Configuration dialog box.
  2. Click the Servers tab.
    In the Name field, enter two underscores and the port number that you want the SAS ODBC server to use. For example, __5001. The SAS Server Type field indicates Local (Single User).
    Servers tab with a local server defined.
  3. Click Configure.
    The Local Options dialog box appears.
    Local Options dialog box
    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\SASHome\SASFoundation\9.3\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\SASHome\SASFoundation\9.3.
    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 __5001 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 value can be either a service name, such as srv1, or it can be a port number prefixed with two underscores, such as __5001. The value cannot be just a port number, such as 5001. 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 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.
  4. Click OK to return to the Servers tab.
    Important! Click Add to save the server definition.
Define the Library
  1. 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.3. 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.
  2. Click Add to save your library information. The library name is added to the list of libraries on the left.
Define the Data Source Name
  1. Click the General tab.
  2. 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.
FOOTNOTE 1:The 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]
Copyright © SAS Institute Inc. All rights reserved.