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

Chapter 4: Using the SAS LIBNAME to Process
Excel Files
4.1 Introduction................................................................................................... 47
4.2 Purpose ......................................................................................................... 48
4.3 Excel-Specific Features of the SAS LIBNAME Statement .............................. 48
4.4 Syntax of the SAS LIBNAME Statement ......................................................... 49
4.5 LIBNAME Statement ENGINE CONNECTION OPTION Descriptions ................ 50
4.5.1 HEADER Option to Read Variable Names ................................................................50
4.5.2 MIXED Option to Select Data Types .........................................................................50
4.5.3 PATH Option to Define Physical File Locations.......................................................51
4.5.4 VERSION Option to Identify Excel File Version........................................................52
4.5.5 PROMPT Option to Interactively Assign a Libref.....................................................52
4.5.6 Other Common SAS PC File LIBNAME Options ......................................................53
4.6 Excel-Specific Dataset Options ..................................................................... 53
4.7 UNIX, LINUX, and 64-Bit Windows Connection Options................................. 54
4.8 Overview of the Examples ............................................................................. 55
4.8 List of Examples .............................................................................................................55
4.9 Examples....................................................................................................... 56
Example 4.1 Using the Engine Connection HEADER Option ..........................................56
Example 4.2 Using the Engine Connection MIXED Option..............................................57
Example 4.3 Using the Engine Connection PATH Option................................................58
Example 4.4 Using the Engine Connection VERSION Option .........................................58
Example 4.5 Using Named Literals with the LIBNAME Statement .................................59
Example 4.6 Using PROC CONTENTS to Examine an Excel Workbook ........................60
Example 4.7 Using Dataset Options to Process Date and Time Values ........................62
Example 4.8 Using Dataset Options to Process Variable Type Conversions................63
Example 4.9 Processing on 64-Bit Operating Systems....................................................64
4.10 Conclusion .................................................................................................. 65
4.1 Introduction
SAS LIBNAME statements are used everywhere in SAS, and they are the most common way for SAS
programmers to access SAS data files. They are used to read SAS data files and write SAS data files. SAS
data comes in. SAS data goes out. But, is that all there is? Well, for SAS users without SAS/ACCESS
Interface to PC Files installed that may be true. But users who want to read Excel files with Base SAS 9
and above and who have SAS/ACCESS Interface to PC Files installed have other options. The LIBNAME
statement will give them direct access to some files that are not SAS files. This tool will allow users to
directly read and write data files formatted for Microsoft Windows applications. Many people are not
aware that SAS/ACCESS Interface to PC Files will allow SAS programmers to access an Excel spreadsheet
the same way as any other SAS file. There are, of course, some restrictions, but there are also a lot of
options that help remove some of the bumps in the road. Most programmers use LIBNAME statements
only to access SAS data files. When external data is required, the data is converted to a text file and input
through a FILENAME connection of some sort. Or, PROC IMPORT or PROC EXPORT is used to process