Automatically read data from different Excel sheets into SAS datasets

[Justin]

Task:

If a Excel file contains numerous different sheets, the sheet names may have blanks and other invalid SAS symbols: (e.g., sales 2007-2012, revenue $2011). Can we read the data from different sheets into different SAS datasets? Can we do it automatically without change the sheet names manually?

options mprint source2 symbolgen; 
 libname SALES Excel "file path\sales.xls"  ver=2002;
 filename SALES "file path\sales.xls"        ver=2002;
  
 proc sql;
 create table Excel_Sheets(keep=libname memname) as
 select *
 from sashelp.VTABLE
 where upcase(libname)='SALES';
 quit;
  
 data AAA;
 length Memname In_Name Out_Name $30;
 set Excel_Sheets;
 In_Name=scan(memname, 1, '.');
 Out_Name=compress(In_Name, '- / # $ *~');
 run;
 
 %macro Read;
 proc sql;
 select count(*) into : N trimmed
 from AAA;
 %let N=&N;
 
 select In_Name, Out_Name into : In1-:In&N,  :OUT1- :OUT&N
 from AAA;
 quit;
 
 %local J;
 %do J=1 %to &N;
 
 data &&Out&J;
 set sales."&&In&J"n ;
 run;
   
 proc import   datafile=Sales  out= &&Out&J DBMS=Excel replace;
 sheet = "&&In&J.$"n;
 getnames = yes;
 mixed = yes;
 run;
  
 %end;
 %mend;
 %Read;
 
 libname sales clear;

Note:
The key challenge in this macro is to use Libname Excel engine and Dictionary or SASHelp. We need them to read the sheet names into a SAS data set, then use it to create macro vars.