You can use the following to find out all excel files under a directory, then pass it to your macro.

295
296
297  data _null_;
298     array files[1000] $ 256 _temporary_;
299     dnum = 0;
300     trunc = 0;
301     call dir_entries("c:\temp", files, dnum, trunc);
302     if trunc then put 'ERROR: Not enough result array entries. Increase array size.';
303     do i = 1 to dnum;
304        if find(files[i],'.xls') or find(files[i],'.xlsx') then put files[i];
305     end;
306  run;

c:\temp\ALSA_INPUT.xls
c:\temp\book11.xlsx
c:\temp\book221.xlsx
c:\temp\b_swap.xls
c:\temp\check_rateSqrt.xls
c:\temp\class.xls
c:\temp\corr_segerr_remove_industr.xlsx
c:\temp\g.xlsx
c:\temp\label_excel.xls
c:\temp\param2.xls
c:\temp\sdata.xlsx
c:\temp\simudata.xlsx
c:\temp\splited.xlsx
c:\temp\test.xls
c:\temp\test.xlsx
c:\temp\testout.xls
c:\temp\vol.xlsx
c:\temp\YIELDCURVE_spot2.xls
c:\temp\_Book1.xls
c:\temp\_Book2.xls

******************************
options cmplib=sasuser.funcs;
proc fcmp outlib=sasuser.funcs.dir;

function diropen(dir $);
   length dir $ 256 fref $ 8;
   rc = filename(fref, dir);
   if rc = 0 then do;
      did = dopen(fref);
      rc = filename(fref);
   end;
   else do;
      msg = sysmsg();
      put msg '(DIROPEN(' dir= ')';
      did = .;
   end;
   return(did);
endsub;

subroutine dirclose(did);
   outargs did;
   rc = dclose(did);
   did = .;
endsub;

subroutine dir_entries(dir $, files[*] $, n, trunc);
   outargs files, n, trunc;
   length dir entry $ 256;

   if trunc then return;

   did = diropen(dir);
   if did <= 0 then return;

   dnum = dnum(did);
   do i = 1 to dnum;
      entry = dread(did, i);
      /* If this entry is a file, then add to array */
      /* Else entry is a directory, recurse. */
      fid = mopen(did, entry);
      entry = trim(dir) || '\' || entry;
      if fid > 0 then do;
         rc = fclose(fid);
         if n < dim(files) then do;
            trunc = 0;
            n = n + 1;
            files[n] = entry;
         end;
         else do;
            trunc = 1;
            call dirclose(did);
            return;
         end;
      end;
      else
         call dir_entries(entry, files, n, trunc);
   end;

   call dirclose(did);
endsub;
run;

Use call execute

%macro read_one(RootPath,FileName,Extension);
libname MyExcel Excel "&RootPath.\&Filename..&Extension";

proc sql noprint;
select catt(trim(libname),'.',quote(trim(memname)),'n')
into: namelist separated by ' '
from dictionary.tables
where libname in ('MYEXCEL');
quit;

%put &namelist;
data &FileName;
set &namelist;
run;
%mend;

%macro readin (dir);
  filename myfiles pipe "dir/b %bquote(&dir.\*.xls*)";
  data af;
    infile myfiles;
        input;
        filename=scan(_infile_,1);
        ext=scan(_infile_,2);
        call execute ('%nrstr(%read_one(%bquote(&dir),'||strip(filename)||','||strip(ext)||');');
        run;
%mend;

非常感谢各位的回复。谢谢