Lesson 2, Part 2: Reading Data from Excel Spreadsheets¶

Method 1: Specifying the XLSX Engine in the LIBNAME Statement¶

The setting of the VALIDVARNAME system option allows the use of column names that contain embedded spaces and special characters.

The LIBNAME statement references the whole Excel file, which is viewed as a SAS library and, the members inside (spreadsheet or named range) are viewed as data files.

The XLSX engine accesses the XLSX file directly when reading the Excel data into SAS. Bitness (32-bit versus 64-bit) does not matter.

The SET statement uses the Excel sheet as an input data file for this data step. Below is the SAS Code.

The last LIBNAME specifies the libref and clear option to disassociate the libref from the SAS library.

In [3]:
options validvarname=any nonotes nosource;
ods html close;
libname XL XLSX 'C:\Explore\SAS\Lesson2\Lesson2Data\Class.xlsx';
data work.class;
  set XL.Sheet1;
run;
libname XL CLEAR; 
proc print data=work.class (obs=5); 
run;
SAS Output

The SAS System

Obs Name Sex Age Height Weight
1 Alfred M 14 69 112.5
2 Alice F 13 56.5 84
3 Barbara F 13 65.3 98
4 Carol F 14 62.8 102.5
5 Henry M 14 63.5 102.5

Method 2: PROC IMPORT¶

In [5]:
options nodate nonumber nodate;
 ods html close;
  PROC IMPORT DATAFILE= 'C:\Explore\SAS\Lesson2\Lesson2Data\Class.xlsx'
     dbms=xlsx REPLACE OUT= work.class_x;
     SHEET="Sheet1";
     GETNAMES=YES;
 RUN;
Title;
 proc print data=work.class_x (obs=5); 
 run;
SAS Output
Obs Name Sex Age Height Weight
1 Alfred M 14 69 112.5
2 Alice F 13 56.5 84
3 Barbara F 13 65.3 98
4 Carol F 14 62.8 102.5
5 Henry M 14 63.5 102.5