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

be used for output to Excel. SAS offers other software besides SAS/ACCESS Interface to PC Files that
enables users to process data from Oracle, and MySQL databases among others. These and other
SAS/ACCESS interfaces will not be discussed here. If you want to find out if you have SAS/ACCESS to
PC Files installed on your system, then execute the SETINIT procedure as shown below.

proc setinit;
run;

Find Out What Hardware and Software Configuration Your Computer Uses.
All of the Windows operating systems since Windows XP are available in both 32-bit and 64-bit versions.
One good thing is that all versions of Excel 2003 and Excel 2007 are 32-bit applications. You can
determine the bit configuration of Excel 2010 by checking FILE>HELP and looking on the right side of the
screen under “About Microsoft Excel”. Similarly, you can determine the bit configuration of Microsoft
Excel 2013 by checking FILE>ACCOUNT>ABOUT EXCEL and looking on the upper right of the pop-up
screen “About Microsoft Excel”. UNIX, LINIX, and other operating systems have their own methods of
detecting the bit configuration in use. This information is important to know when selecting options to use
with the LIBNAME statement.

What Can the LIBNAME Statement Do for You?
When you use a SAS LIBNAME to read Excel data, the data can be read directly from native format binary
Excel files. The SAS LIBNAME can read data either from a full Excel worksheet or from a part of an
Excel worksheet defined by Excel to be a “named range”. Within Excel, a “named range” can be as small
as a single cell, or as large as the whole worksheet. That same data can be written out in nearly the same
way directly to a native Excel binary file. Named ranges can be written out in similar sized units, but not
necessarily in the same location. All “named range” output that is written begins in cell A1. Some
assumptions are made using LIBNAME statements on the input side and the output side, but knowing the
rules can make things easier. Writing data from a SAS file to Excel is generally a little safer. This is
because when a SAS file is written out, all of the data in one variable or Excel column is of the same data
type. Reading data from Excel is sometimes more of a challenge. The ability of Excel files to change data
formats on the data cell level can still present challenges to SAS programs reading input from Excel files.
SAS input routines predict the type of data that is in an Excel worksheet column, but generally by only
looking at the first few rows of the column. This tends to produce missing data values for columns where
both numeric and character values exist. In some cases, this issue can be overcome if named ranges are
defined within the Excel worksheet. But full columns of mixed values present a challenge. The MIXED
option described below can help to modify this behavior and make your results more consistent with data of
unknown types from the spreadsheets. Also, be aware that some configurations of the Microsoft JET/ACE
engines may be limited to 255 columns and 65,535 rows.

4.4 Syntax of the SAS LIBNAME Statement
First, let us look at the different syntax options and parameters available for the LIBNAME statement as
they relate to accessing Microsoft Excel files. Some general LIBNAME options are discussed in this
chapter. The SAS/ACCESS engine connection options change the way that the LIBNAME statement
interacts with Excel workbooks. But the SAS/ACCESS LIBNAME options work more directly with the
data in the worksheets.

LIBNAME libref < engine > < physical-file-name >
< SAS/ACCESS-engine-connection-options >
< SAS/ACCESS-libname-options >;

The following engine connection options are commonly used by SAS when opening Excel files. These
items will be discussed later below; other, less used options also exist.

HEADER Affects the way that the first row or observation is processed.

●

MIXED Affects how columns with both numeric and character data are treated.

●

PATH Allows you to supply a path for an Excel file, this is not always needed.

●