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

Chapter 9: Accessing Excel with OLE DB or ODBC
Application Program Interfaces (API Methods)
9.1 Introduction................................................................................................. 149
9.2 Purpose ....................................................................................................... 149
9.3 Concept of the OLE DB or ODBC API Processes ......................................... 149
9.4 Guidelines for Setting Up OLE DB or ODBC Connections ............................ 150
9.5 List of Examples.......................................................................................... 150
9.6 Examples..................................................................................................... 151
Example 9.1 Assign a Libref to an Excel Worksheet with the OLE-DB Dialog Box ....151
Example 9.2 Using LIBNAME Prompt Mode to Build an OLE-DB Connection............152
Example 9.3 Using an OLE-DB init_string to Open an Excel Workbook......................154
Example 9.4 Using PROC CONTENTS to Verify Excel to OLE DB Connection ...........154
9.7 Conclusion .................................................................................................. 156
9.1 Introduction
The ability of SAS to reference external data sources has been enhanced by having access to application
program interface (API) standards and implementations. SAS uses the Microsoft API interface routines
(along with others) to communicate between SAS and other database systems. We are interested in the
ability to communicate with the Microsoft application Excel. OLE DB is an API interface that can interface
with many applications. The really great thing is that, as SAS programmers, we do not need to know how
to access these API routines, or anything about them. API routines are the building blocks of an operation
system. They allow an application to be programmed by using a standard interface--hence the name
Application Program Interface. By opening an API through the OLE DB interface, a programmer could
have direct SQL access to an OLE DB object, like an Excel workbook.
9.2 Purpose
This chapter shows you how to access Excel files from SAS using the OLE DB or ODBC API interfaces.
While newer methods do exist, these are being included here because they are still a viable method of
accessing Excel data from SAS.
9.3 Concept of the OLE DB or ODBC API Processes
One of the first places, and perhaps the main place, that the OLE DB interface surfaces is in the LIBNAME
statement. There are specific options that address the OLE DB interface to Excel. Options that apply to
OLE DB access to files other than Excel files are not shown here, but are easy to find in the SAS Help
menus. Select “OLEDB” or “OLE DB” and the name of the database you are using. Options that apply to
Microsoft JET or ACE providers are likely to be able to access Excel files. You can connect to OLE DB
services or directly to the data provider, like the Microsoft JET or ACE providers. The syntax of the OLE
DB options for the LIBNAME statement is shown below.