Accessing Excel with OLE DB or ODBC Application Program Interfaces (API Methods) - Exchanging Data Between SAS and Microsoft Excel - page 150

9.4 Guidelines for Setting Up OLE DB or ODBC Connections
OLE DB LIBNAME syntax is shown below in Table 9.1. The engine name OLEDB is required to access
the OLE DB methods.

LIBNAME libref OLEDB <connection-options> <LIBNAME-options>;

Table 9.1: OLE DB LIBNAME Syntax Options.

Object Description

Libref This is a one- to eight-character SAS name that is used to reference

the connection within the SAS code. This may point to a database,
schema, server, tables, or a view supported by another system.

OLEDB This is a constant that indicates that the OLE DB tools will be used

to reference the external data files. The connection options and
LIBNAME options are not required to be submitted to activate the
LIBNAME statement.
A dialogue box will open and allow you to enter the required values
when only the following is submitted:
LIBNAME libref OLEDB;

< connection-options > These are options that specify how and where to connect to the

external data source.

<LIBNAME- options > These options are used to decide how to tune performance, access

external records, and determine how to deal with variable names and
other tasks.

The examples in this chapter show how to connect to an OLE DB database management system. Space
does not permit showing an example of each option. Generally speaking, there are two methods to connect
with OLE DB. One is interactively by using the PROMPT= connection option (or getting it to appear
because you omitted required information). Additional packages exist that allow interaction with other
DBMS processing, but they are not discussed here. The following SAS software is needed to use the
features explained in this chapter that access Excel files from SAS:

● SAS/ACCESS Interface to PC Files
● SAS/ACCESS Interface to ODBC
• SAS/ACCESS Interface to OLE DB

9.5 List of Examples

Figure 9.5.1: List of Examples for PROC EXPORT.

Example
Number

General Description

9.1 Assign a Libref to an Excel Worksheet with the OLE-DB Dialog Box. This example

shows the steps to assign an OLE DB connection to a libref.

9.2 Using LIBNAME Prompt Mode to Build an OLE-DB Connection. This example shows

how to capture the init_string into the SAS log. The example also assigns the libref and
opens the Excel workbook.

Using an OLE-DB init_string to Open an Excel Workbook. This example shows how to
use the init_string to assign a file to the libref and open the Excel workbook.

9.3

Using PROC CONTENTS to Verify Excel to OLE DB Connection. This example prints
the output from PROC CONTENTS.

9.4