DATA Step, Macro, Functions and more

Importing all Excel worksheets into SAS

Reply
New Contributor
Posts: 4

Importing all Excel worksheets into SAS

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

PROC Star
Posts: 7,468

Re: Importing all Excel worksheets into SAS

Posted in reply to RishiThakur

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;

Super User
Posts: 10,020

Re: Importing all Excel worksheets into SAS

Posted in reply to RishiThakur

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

Super User
Posts: 3,250

Re: Importing all Excel worksheets into SAS

Posted in reply to RishiThakur

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

New Contributor
Posts: 4

Re: Importing all Excel worksheets into SAS

Posted in reply to 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

PROC Star
Posts: 7,468

Re: Importing all Excel worksheets into SAS

Posted in reply to RishiThakur

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

Super User
Posts: 3,250

Re: Importing all Excel worksheets into SAS

Posted in reply to RishiThakur

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.

Ask a Question
Discussion stats
  • 6 replies
  • 465 views
  • 2 likes
  • 4 in conversation