use sas libname engine for excel, like[pre] libname ex 'your.work.book.xls' ;[/pre]Unfortunately, to copy all sheets into the work library:[pre] proc copy in= ex out= work ;
run ; [/pre]didn't work for me. Not even with [pre]option validvarname= ANY ;[/pre]However, this copies sheet1[pre]data s1; set ex."sheet1$"n ; run;[/pre]So, you could use a macro loop, or more simple CALL EXECUTE(), like[pre] data _null_ ;
do ns= 1 to 24 /*yoursheet count*/ ;
ds = 'S' !! put( ns, 2.-L );
sh = 'sheet' !! put( ns, 2.-L );
call execute( 'data ' !! ds ) ;
call execute( '; set ex."' !! trim(sh)!! '$"n ; run; ' ) ;
run;[/pre]...... it works for me.
Then you can work with the 24 data sets in the work library named S1 to S24.