Reading multiple sheets from Excel in SAS

The code below let’s you import the sheets found in an Excel-file to SAS datasets.

ExcelFile: The Excell-file containing the sheets that you want to import.
Prefix: Prefix of the datasets imported into work.

%macro ReadExcelSheets(ExcelFile=, Prefix=, OutLib=work);

     libname XLData "&ExcelFile." access=readonly;

     %let Delimitor = ยค;

     proc sql noprint;
       select compress(memname,"$") into :Sheets separated by '&Delimitor.'
             from dictionary.tables
             where libname = "XLDATA" and upcase(memname) like '%$'

     %put Sheets          : &Sheets.;
     %let NumberOfSheets = %sysfunc(countw(&Sheets., &Delimitor.));
     %put Number of sheets: &NumberOfSheets.;

     %do J=1 %to &NumberOfSheets.;
         %let Sheet = %scan(&Sheets., &J, &Delimitor.);
         %put Processing: &Sheet.;

         %if %length(&Prefix) %then %let Prefix = &Prefix._;
         proc import out=&OutLib..&Prefix.&Sheet. datafile="&ExcelFile."
                dbms=xls replace;


     libname XLData clear;
%mend ReadExcelSheets;

%ReadExcelSheets(ExcelFile=c:\temp\excelfile.xls, Prefix=test);