topic Re: Importing Excel with 24 sheets in SAS Procedures

Importing Excel with 24 sheets

R_Win — Mon, 06 Apr 2009 08:52:56 GMT

Hi

i have a Excel in this i have 24 sheets sheet1 to sheet24 all this sheets have same variables how can i import all the sheets at once.

Re: Importing Excel with 24 sheets

Peter_C — Mon, 06 Apr 2009 10:24:30 GMT

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; ' ) ;
end;
run;[/pre]...... it works for me.
Then you can work with the 24 data sets in the work library named S1 to S24.

good luck

PeterC

Re: Importing Excel with 24 sheets

Andre — Mon, 06 Apr 2009 11:06:43 GMT

a little addon is neccessary in case your nine first sheet names are
written as sheet01-sheet09 in place of sheet1-sheet-9 sheet10-sheet24

Change this line in forwarded code
sh = 'sheet' !! put( ns, z2.-L );

Andre