Using the SAS LIBNAME to Process Excel Files - Exchanging Data Between SAS and Microsoft Excel - page 48

external files. The most popular of these applications (and the focus of this topic) is moving data between
SAS files and Microsoft Excel. The LIBNAME statement allows the user to define an Excel file in SAS
terms. This gives the programmer access to LIBNAME and dataset options to control how the Excel file is
defined, accessed and, yes, even how the data will be formatted. Not all of the data formats of Excel can be
accessed, but the LIBNAME statement gives the SAS user more control over the data.
Complexities That Relate to SAS, Excel, and Your Hardware
Before we get started on the basics about the LIBNAME statement as it relates to SAS, Excel, and your
hardware, I need to make a few comments. To paraphrase the beginning of Dickens' novel A Tale of Two
Cities : it is the best of times, it is the worst of times, as far as accessing Excel data from SAS goes. Why
would I, as an author who has spent the last several years working on this project, feel that way? The
increase in options relating to hardware (32-bit and 64-bit); the availability of 32-bit and 64-bit SAS 8.2,
9.0, 9.1, 9.2, 9.3, to 9.4; and the changes in the Excel version formats from Excel 2003, to Excel 2007 and
the addition of 2010 (32-bit and 64-bit), and 2013 (32-bit and 64-bit) present a very large number of
combinations. The examples I present here may not work on your configuration.
NOTE: IMPORTANT RESTRICTION. In the past, multiple copies of some SAS software versions
were able to reside on one computer, but the SAS PC Files Server (pcfservice.exe) for SAS 9.2 and SAS
9.3 have the same name, run as the same service, listen for commands on different computer I/O ports, and
are NOT compatible. So, even though you can install both SAS 9.2 and SAS 9.3 on the same computer
only one version of SAS will be able to read and write to Excel files using the SAS PC Files Server.
4.2 Purpose
SAS is a language of defaults; the runtime system looks to see how a variable is first used. Then, it assigns
a default type to the variable. When a programmer types “PROC PRINT; RUN;” the last SAS dataset
created is sent to the Output window with all of the observations and all of the variables listed. The SAS
language also gives the programmer choices about the code. The ATTRIB command assigns either a
numeric or a character data type to a variable. These assignments can change the size of the variable to a
small numeric with a limited range of values, or a large character variable bigger than the default.
LIBNAME statements are no different; they have options to change the behavior of the input/output
processes. This chapter addresses some options available for reading and writing data to and from Excel
files using SAS.
4.3 Excel-Specific Features of the SAS LIBNAME Statement
What Is the “LIBNAME” Feature for Excel Files?
The LIBNAME is the SAS door that opens onto Excel data, making it look like SAS data, allowing the
SET statement (and dataset options) to directly read and write Excel file data. This allows data to be input
or output by a DATA step or SAS procedure. SAS uses either the Microsoft JET or ACE SQL engine to
read and write to the Excel files. The LIBNAME statement employs special options to help make the task
easier. Of course, options, while being nice to have, often imply freedoms (or restrictions).
NOTE: Excel uses binary formats for Excel file versions Excel 5.0 to Excel 2003. However, Excel 2007,
Excel 2010, and Excel 2013 do not produce a native binary formatted file. These versions generate files
that are output in a new file format called Open XML. The Open XML files have a file name with the
extensions “xlsx”, “xlsm”, and “xlsb”. But, Microsoft Excel 2007, Excel 2010, and Excel 2013 can read
and write to other Excel version formats. Changing an Excel 2007 or later file extension to *.zip will allow
you to view the XML parts of the Excel files.
What Is Needed to Use a SAS LIBNAME to Access Excel Files?
The ability to use a LIBNAME to access data has long been restricted to SAS datasets. Beginning with
SAS 9, SAS/ACCESS Interface to PC Files can use Excel files as SAS input. Furthermore, SAS files can