If your excel file has extension ".xlsx", I think you can use this method. Well, this program is for single file, you may want to modify it for multiple files condition:
%let excelfile=C:\Profiles\Work\New Excel Worksheet.xlsx;
/*%let excelfile=C:\Profiles\Work\New Excel Worksheet_0row_edited.xlsx;*/
/*%let excelfile=C:\Profiles\Work\New Excel Worksheet_1row.xlsx;*/
filename excel zip "&excelfile";
data tab1;
length memname $256;
fid=dopen("excel");
if fid=0 then stop;
memcount=dnum(fid);
do i=1 to memcount;
memname=dread(fid,i);
output;
end;
fid=dclose(fid);
run;
data tab2;
set tab1(keep=memname) end=eof;
where memname=:"xl/worksheets/" and scan(memname,-1,'.')='xml';
file=scan(memname,-1,'/');
rc=dosubl('
option nonotes;
data tab3;
infile excel('||trim(memname)||') lrecl=32767 recfm=f truncover;
input text $32767.;
if prxmatch("/<v>.*?<\/v>/",trim(text)) then do;
output;
stop;
end;
run;
%let m'||cats(_n_)||'=&sysnobs;
option notes;
');
rownum=symget(cats('m',_n_));
if rownum>'0' then put "The Excel file &excelfile is not empty.";
else put "The Excel file &excelfile is empty.";
run;
The unzip xlsx file skill, I learned from this post: Using FILENAME ZIP to unzip and read data files in SAS - The SAS Dummy
... View more
