Database Reference
In-Depth Information
3.4 Data Access Methods for Excel Files Supported by PROC IMPORT
The data access methods listed in Figure 3.4.1 are used to read data files Excel has the ability to create.
Selecting a DBMS mode determines which utility will be used to process the external file to create an output
SAS dataset. The input file may be a text file or an Excel spreadsheet. See the documents listed above for more
details about the SAS software version you are using. Some of these data access methods (the DBMS=modes)
require SAS/ACCESS Interface to PC Files software to function. You must have SAS/ACCESS Interface to PC
Files licensed before you can import files directly from some versions Microsoft Excel workbooks. Some
features relating to Microsoft Excel 2007, Excel 2010, and Excel 2013 when using Microsoft Windows,
LINUX, and UNIX operating systems may not be available in SAS versions prior to the third maintenance
release of SAS 9.2. Because the number of SAS, Excel, and operating system versions is large, I once again
refer you to the SAS documentation to help you figure out what you have installed.
If you suspect that your SAS and Excel software may have different bit configurations (32 or 64 bit), contact
your IT Department.
The DBMS identifiers listed in Table 3.4.1 are relative to the file formats that Microsoft Excel can read or write.
The SAS documentation lists other DBMS identifiers that the PROC IMPORT can read. See the SAS
documentation for your version of SAS for other options to read file formats available. Different versions of
SAS may not be able to read to all of the versions of Excel.
Table 3.4.1: DBMS Formats Available for Input.
DBMS
Identifier
SAS/ACCESS
Interface to PC Files
Required
General Description of the DBMS Output File
CSV
N
Text file with a comma delimiter
TAB
N
Text file with a tab delimiter
DLM
N
Text file with a user-defined delimiter
EXCEL
Y
Excel workbook (2003 xls - 2013 xlsx)
EXCELCS
Y
Excel workbook (2003 xls - 2007 xlsx) using the SAS
PC Files Server
EXCEL4
Y
Excel workbook using PROC DBLOAD
EXCEL5
Y
Excel workbook using PROC DBLOAD
XLS
Y
Excel workbook using file formats prior to Excel 2007
except Excel 4 and Excel 5
XLSX
Y
Excel workbook using file formats 2007, 2010, and 2013
Table 3.4.2 lists some information about the input methods available when reading Excel worksheets. Some of
these methods have limitations that are smaller than the full capabilities of the Excel version that created them.
These restrictions are as a result of using the Microsoft JET or ACE engines to access the Excel workbooks.
Table 3.4.2: DBMS Input Methods of Accessing Excel Files.
Utility
DBMS Model
Excel Version
Comments
EXCEL
LIBNAME statement
5, 95, 97, 2000,
2002, 2003, 2007,
2010, 2013
This DBMS option will use the LIBNAME
statement. Depending upon your version of
SAS and Excel, access may be limited to the
first 65,535 rows and 255 columns.
EXCELCS
SAS PC Files Server
5, 95, 97, 2000,
2002, 2003, 2007,
2010, 2013
This DBMS option will use the SAS PC
Files Server. Depending upon your version
of SAS and Excel, access may be limited to
the first 65,535 rows and 255 columns.
Search WWH ::
Custom Search