Tag: Sheets

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 '%$'
       ;
     quit;

     %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;
                sheet="&Sheet.";
                getnames=yes;
         run;

     %end;

     libname XLData clear;
%mend ReadExcelSheets;

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