%let dir=D:;
%let name=test.xlsx;
%macro ReadXls(name);
libname excellib excel "&dir.\&name.";
/*以上就是些普通设置 */
proc sql noprint;
create table sheetname as
select tranwrd(memname, "''", "'" ) as sheetname
from sashelp.vstabvw
where libname= "EXCELLIB";
/*从sashelp.vstabvw表中取得sheetname,顺便把双引号变成单引号，意义不明*/
select count(DISTINCT sheetname) into :number
from sheetname;
%put &number;
quit;
/*看看有几张sheet,赋值给number*/
select DISTINCT sheetname into :sheet1 -:sheet%left(&number) from sheetname;
/*%put &sheet1;*/
/*把表名赋值给sheet1-...-sheetn;请不要把%和left分开 */
quit;

libname excellib clear;
/*取消libname*/
/*%do i=1 %to &cnt_sht;*/
%do i=1 %to &number;
/*&cnt_sht应该指&number吧*/
proc import datafile= "&dir.\&name"
out=sheet&i replace;
sheet= "&&sheet&i";
getnames=yes;
mixed=yes;
run;
/*读取sheet数据*/
proc append base=master data=sheet&i force;
run;
/*拼接sheet1-..-sheetn*/
%end ;

%mend ReadXls;

%ReadXls (test.xlsx)