Editor's note: we modified this popular topic reply to be more relevant for current readers.
You cannot INFILE an XLSX file in DATA step. You need to use an engine that can read the data structure of an Excel spreadsheet.
If you have SAS/ACCESS to PC Files, you can use PROC IMPORT DBMS=XLSX or LIBNAME XLSX to accomplish this. This works on machines even where Excel is not installed (such as Linux). Both of these methods work in SAS University Edition if you're using that.
If you don't have SAS/ACCESS to PC Files licensed/installed, and you do have Excel installed on a local Windows machine along with SAS, you can try to use DDE. That's a method of using SAS to "talk to" Excel to exchange information about your data. Note that DDE typically won't work when SAS is running on a remote server.
More on DDE:
1) Read Koen Vyverman's excellent DDE papers (here's one).
2) Excel is not a text file, so INFILE'ing it as ASCII text won't yield much:
%let ws = Sheet1 ;
%let r1 = 7 ;
%let c1 = 2 ;
%let r2 = 534 ;
%let c2 = 23 ;
FileName XL
DDE
"Excel|&ws!r&r1.c&c1.:r&r2.c&c2."
LRecL = 5000
NoTab
;
Data specs ;
Infile XL
DSD
Pad
DLM = "09"x
;
Length Col1 - Col%eval( &c2. - &c1. - 1 ) $ 200 ;
Input Col1 - Col%eval( &c2. - &c1. - 1 ) ;
Run ;
HTH,
Kevin
