topic Re: Importing all Excel worksheets into SAS in SAS Programming

Importing all Excel worksheets into SAS

RishiThakur — Fri, 28 Jun 2013 14:22:08 GMT

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

Re: Importing all Excel worksheets into SAS

art297 — Fri, 28 Jun 2013 16:01:27 GMT

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;

Re: Importing all Excel worksheets into SAS

Ksharp — Sat, 29 Jun 2013 13:35:27 GMT

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

Re: Importing all Excel worksheets into SAS

SASKiwi — Sun, 30 Jun 2013 20:23:18 GMT

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

Re: Importing all Excel worksheets into SAS

RishiThakur — Mon, 01 Jul 2013 08:29:26 GMT

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

Re: Importing all Excel worksheets into SAS

art297 — Mon, 01 Jul 2013 14:50:32 GMT

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

Re: Importing all Excel worksheets into SAS

SASKiwi — Tue, 02 Jul 2013 00:24:43 GMT

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.