Easy Data Movement between SAS and Microsoft Excel - Exchanging Data Between SAS and Microsoft Excel - page 3

engines that read and write to Excel workbooks have limitations that may not allow for processing of all
data in an Excel workbook when using a SAS LIBNAME statement.

Table 1.2.1: Microsoft Excel Workbook File Limitations.

Version Limited to One

Sheet

Rows Columns Extension

EXCEL4 YES 16,384 256 * .xls
EXCEL 5 NO 16,384 256 * .xls
EXCEL 95 NO 16,384 256 * .xls
EXCEL 97 NO 65,536 * 256 * .xls
EXCEL 2000 NO 65,536 * 256 * .xls
EXCEL 2002 NO 65,536 * 256 * .xls
EXCEL 2003 NO 65,536 * 256 * .xls
EXCEL 2007 NO 1,048,576 * 16,384 * .xlsx, xlsb, xlsm
EXCEL 2010 NO 1,048,576 * 16,384 * .xlsx, xlsb, xlsm
EXCEL 2013 NO 1,048,576 * 16,384 * .xlsx, xlsb, xlsm
* The limits listed here are the limits for rows and columns of Excel files. However, the Microsoft JET
and ACE engines used by the LIBNAME statement to access Excel files for reading and writing data
are limited to 65,535 rows and 255 columns. Files larger than 255 columns and 65,535 rows should be
processed without using the LIBNAME statement or any method that uses the Microsoft JET or ACE
engines to refer to the Excel files.

1.2.4 Excel Workbook Formatting Groups

File Format Groups
● Excel 4 is unique with only one sheet and is the oldest of supported Excel formats. This format
may not be supported in future versions of Excel.
● Excel 5 and Excel 95 share the same format.
● Excel 97, Excel 2000, Excel 2002, and Excel 2003 share the same format.
● Excel 2007, Excel 2010, and Excel 2013 share the same format and are the newest versions of
Excel files.
• Excel 4 - Excel 2003 are binary formats, but as noted above they are not the same format.

NOTE: Newer Excel software can read and write any older Excel format.

Excel 2007, Excel 2010, and Excel 2013 file formats:
● .xlsx format does not allow Visual Basic for Applications (VBA) macros to be stored within the
spreadsheet.
● .xlsm format allows (VBA) macros to be stored within the spreadsheet.
● .xlsb format stores the data in a binary format.
• .xlsx, .xlsm, and .xlsb formatted files are .ZIP files of XML documents that comprise the Excel
workbook. .xlsb formatted files also contain binary formatted elements. While these files may also
contain style sheets, images and other components, this topic generally addresses the data contents
unless otherwise specified.

Most Excel programs will open delimited or .xml files with a double click, but these are not Excel
formatted files—they are text files that Excel knows how to format.