BookmarkSubscribeRSS Feed
RishiThakur
Calcite | Level 5

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

6 REPLIES 6
art297
Opal | Level 21

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
Super User

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
PROC Star

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
Calcite | Level 5

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
Opal | Level 21

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
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 2481 views
  • 2 likes
  • 4 in conversation