Use PROC IMPORT to Read External Data Files and Excel Workbooks into SAS - Exchanging Data Between SAS and Microsoft Excel - page 31

Chapter 3: Use PROC IMPORT to Read External Data
Files and Excel Workbooks into SAS

3.1 Introduction.........................................................................................................31
3.2 Purpose ...............................................................................................................32
3.3 Syntax of the SAS IMPORT Procedure .................................................................32
3.4 Data Access Methods for Files Supported by PROC IMPORT ..............................33
3.5 Overview of the Examples ...................................................................................34
3.6 List of Examples..................................................................................................34
Example 3.1 PROC IMPORT Using the DBMS=EXCEL4 or EXCEL5 Option........................35
Example 3.2 PROC IMPORT Using the DBMS=DLM Option .................................................35
Example 3.3 PROC IMPORT Using the DBMS=EXCEL Option .............................................37
Example 3.4 PROC IMPORT Using the DBMS=EXCELCS Option ........................................40
Example 3.5 PROC IMPORT Using the DBMS=XLS or XLSX to Select Columns ...............42
Example 3.6 PROC IMPORT Using the DBMS=XLS or XLSX to Select Rows .....................43
Example 3.7 PROC IMPORT Using the DBMS=XLS or XLSX to Select Excel Ranges .......44
3.7 Conclusion ..........................................................................................................45

3.1 Introduction
This chapter builds upon the Chapter 1 explanation and examples of the SAS Import Wizard, and will explain
the syntax, usage, and the results that can be generated when using the SAS IMPORT procedure, specifically
PROC IMPORT.

PROC IMPORT is a general purpose routine and is able to read data from text files and Excel workbook files
which can exist in several different formats. The ability to read files of many formats makes PROC IMPORT
extremely useful. The primary focus of this chapter will be upon reading Excel files. However, some examples
will show how to read text files with delimiters because Excel can write files with those formats. The syntax of
PROC IMPORT will be explained and the options listed below in Table 3.3.1. One important aspect of PROC
IMPORT is its ability to interface with an external Data Base Management System (DBMS). PROC IMPORT
has a syntax argument called DBMS that makes this option available and permits access to many different input
data formats. Options exist to enable the transfer of data between SAS and many other file formats, but because
the focus of this work is moving data from SAS to Excel and back, only options relative to Excel will be
explored.

Depending upon which operating system and version of SAS you are using, you may be able to read some or all
of the following formats. The details for reading these other formats are explained in the SAS documentation.

Microsoft Access database files

●

Microsoft Excel workbook files ●

Lotus 1-2-3 spreadsheet files

●

Paradox files

●

SPSS files

●

Stata files

●

dBase files

●