06-28-2013 10:22 AM
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';
infile excellib truncover end=last;
/* Edit length as needed */
length fname $20;
if last then call symput('total',trim(left(put(i,15.))));
%do i=1 %to &total;
proc import datafile="F:\IMN\&&pext&i"
/* Invoke the macro */
Any help is appreciated.
06-28-2013 12:01 PM
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' ;
CREATE TABLE sheets AS
SELECT distinct memname FROM DICTIONARY.COLUMNS
WHERE libname='EXBK' AND memtype='DATA' and index(memname,'$');
LIBNAME exbk clear;
06-29-2013 09:35 AM
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;
06-30-2013 04:23 PM
Have a look at the macro I put into another post. It uses the Excel libname to automatically copy all sheets using PROC DATASETS:
07-01-2013 04:29 AM
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?
07-01-2013 08:24 PM
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.