Importing all Excel worksheets into SAS

[RishiThakur]

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

[art297]

I don't have time to formalize the solution, but you could just expand on the code you already have;  You are currently piping in all of the workbook names and your macro already loops through all of the workbooks.

Within that loop you could assign the workbook to a libref, run a proc contents with an out= option to create a file of the various sheet names, and then simply include a loop that goes through those names and including them in your proc import using the sheet= option.

Similarly, rather than running proc contents, the following snippet from the U of Oregon does the same thing using proc sql:

LIBNAME exbk excel 'c:\sas\excel\test.xls' ;
PROC SQL;
CREATE TABLE sheets AS
SELECT distinct memname FROM DICTIONARY.COLUMNS
WHERE libname='EXBK' AND memtype='DATA' and index(memname,'$');
QUIT;
 LIBNAME exbk clear; 

[Ksharp]

Add something more into Arthur's code :

libname x excel 'c:\temp\xx.xls';

data _null_;
 set sashelp.vmember(where=(libname='X'));
 call execute('data work.n'||strip(_n_)||'; set x.'||quote(dequote(memname))||'n;run;');
run;

Ksharp

[SASKiwi]

Have a look at the macro I put into another post. It uses the Excel libname to automatically copy all sheets using PROC DATASETS:

https://communities.sas.com/message/169998#169998

[RishiThakur]

Thank you for your valuable responses. However I didn't use the libname method as it's not working on my side.

Is there a way to extract the Excel worksheets not using the libname Excel statement?

Regards,

Rishi

[art297]

If you are on a version of Windows, you can use DDE.  Take a look at: http://analytics.ncsu.edu/sesug/2003/TU15-Vyverman.pdf

[SASKiwi]

I'm curious to know why the Excel LIBNAME does not work for you while PROC IMPORT from Excel does. Both should work fine if your SAS environment is set up correctly for Excel, and SAS/ACCESS to PC FILES is installed and licensed.