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

Example Number General Description

Server to be processed. The main point of these code routines is to
show how to read parts of worksheets within one workbook, and to
change variable names and labels as the data is read from Excel into a
SAS dataset.

PROC IMPORT Using the DBMS=EXCELCS Option. This
example shows code that was executed on a 64-bit operating system
using a 64-bit copy of SAS 9.3 and a 32-bit copy of Microsoft Excel.
Since this computer operating system and SAS use a 64-bit
configuration but Excel uses a 32-bit configuration, PROC IMPORT
requires the use of the SAS PC Files Server. The “CS” part of
DBMS=EXCELCS annotates this feature is in use.

3.4

3.5 PROC IMPORT Using the DBMS=XLS or XLSX to Select

Columns. This example reads an Excel worksheet with no column
headers (variable names) in the output Excel worksheet. It also
demonstrates that PROC IMPORT will read an Excel sheet name with
spaces.

3.6 PROC IMPORT Using the DBMS=XLS or XLSX to Select Rows.

Reading Excel data from selected rows of an Excel worksheet.

3.7 PROC IMPORT Using the DBMS=XLS or XLSX to Select Excel

Ranges. This example shows you how to use PROC IMPORT to read
a range of cells from an Excel worksheet.

Example 3.1 PROC IMPORT Using the DBMS=EXCEL4 or EXCEL5 Option
The SAS IMPORT procedure maintains the backward compatibility features required to process Excel
workbooks in the Excel 4 and Excel 5 formats. This example shows how to write Excel files in those formats.
For Excel 4 workbooks the sheet name is the same as the file name (without the .xls) and there is only one sheet
in the workbook. For Excel 5 formatted workbooks, the sheet name is “Sheet1”.

* SAS code to import data from an Excel4 file.;
* there is only one sheet in Excel4 files;
PROC IMPORT
DATAFILE='C:\My_Files\shoes_to_Excel_4_file.xls'
DBMS=EXCEL4
OUT=shoes_from_Excel_4
REPLACE;
RUN;

* SAS code to import data from an Excel 5 file.;
PROC IMPORT
DATAFILE='C:\My_Files\shoes_to_Excel_5_file.xls'
DBMS=EXCEL5
OUT=shoes_from_Excel_5
REPLACE;
RUN;

Example 3.2 PROC IMPORT Using the DBMS=DLM Option
Using PROC IMPORT to read delimited files in Base SAS invokes the External File Interface (EFI), and the
following code reads in a delimited file with commas as the delimiter from the external file named Shoes.csv in
directory c:\My_files. This example uses the DBMS=DLM option with the DELIMITER=',' option to select a
comma for the delimiter. In addition, it uses the DATAROW=1 and GETNAMES=NO options. These options
cause the input SAS file to make the first row from the *.csv file appear as data in the SAS file.