Dear all, I have a code which extract multiple XLS files from a specific folder and imports them into SAS. However the XLS files have the same layout and multiple worksheet, how can I import all worksheet at once into SAS? I have now hardcoded the sheetname into the code as seen below SHEET="AREA-W"; I would like a dynamic version where SAS will read all worksheet. The layout of the worksheets is identical. How can I achieve this? Here is the code I use: filename excellib pipe 'dir F:\IMN /b'; data _null_; infile excellib truncover end=last; /* Edit length as needed */ length fname $20; input fname; i+1; call symput('fname'||trim(left(put(i,20.))),scan(trim(fname),1,'.')); call symput('pext'||trim(left(put(i,20.))),trim(fname)); if last then call symput('total',trim(left(put(i,15.)))); run; %macro importExcel; %do i=1 %to &total; proc import datafile="F:\IMN\&&pext&i" out=work.&&fname&i dbms=XLS replace; SHEET="AREA-W"; getnames=YES ; run; %end; %mend; /* Invoke the macro */ %importExcel Any help is appreciated. Regards, Rishi
... View more