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

Remember, Excel 2007 and earlier are all 32-bit applications. Some of the options for UNIX and LINUX
are not displayed here because they are site-specific ( CONNECT_STRING, DSN, PORT, PASSWORD,
PATH, PORT, SERVER, SERVERUSER, and SSPI ).

LIBNAME xls_V97 PCFILES PATH='C:\My_excel_files\my_excel_file_V97.xls' ;
LIBNAME xls_V07x PCFILES PATH='C:\My_excel_files\my_excel_file_V07.xlsx';
LIBNAME xls_V07b PCFILES PATH='C:\My_excel_files\my_excel_file_V07.xlsb';
DATA xls_V97.'sheet1'n
xls_V07x.'sheet1'n /* writes a *.xlsb formatted file not *.xlsx */
xls_V07b.'sheet1'n;
Set sashelp.shoes;
RUN;

4.10 Conclusion
The SAS LIBNAME statement is a very powerful tool, with many options that are opened up by having
SAS /ACCESS Interface to PC Files available. Since the release of SAS 9.2 TS Level 2M3, SAS users
again have access to read and write all versions of Microsoft Excel on a 32-bit Windows system and also
on the UNIX, LINUX, Windows 64-bit, and Windows 64-bit server systems. In addition, the Excel
application has been upgraded to run in a 64-bit mode. The connection options that process an Excel
workbook on a Windows 32-bit or 64-bit OS are critical to accessing Excel data. Not shown here: how to
read and write Excel files using PROC SQL. That is reserved for Chapter 10. The connection options from
Chapter 10 allow PROC SQL to find and access Excel files. This chapter looked only at the options that
apply directly to Microsoft Excel files on a PC. SAS/ACCESS Interface to PC Files will also process
Microsoft Access files. The LIBNAME and DATASET options described here enhance access to data files
that are not SAS files. The DATASET options are available in DATA steps, in SAS procedure executions,
and in PROC SQL processing, thus pushing the power of the LIBNAME engine far beyond the
Import/Export engines of Base SAS. Below is a summary of the options explained. However, it is not an
exhaustive list of all options available.

Engine Connection Options
• HEADER option

controls processing of the first line of Excel worksheets.

• MIXED option

controls conversion of input Excel data into character variables.

• PATH option

assists in locating files for processing.

• VERSION option

controls the output version of an Excel workbook.

Examples Using Selected Dataset Options
• Using Named Literals with the LIBNAME statement.
• Using PROC CONTENTS to Examine an Excel workbook.
• Using Dataset Options to Process Date and Time Values.
• Using Dataset Options to Process Variable Type Conversions.
• Processing on 64-bit Operating Systems.