The real point of this exercise is to be able to read an unkown number of sheets of unkown name inside a workbook. By making the Excel workbook a library, you can use proc datasets to write the list of sheets to a SAS table. Then you can use a macro to loop thorugh the table and read all the sheets without having to know before hand how many there are or what their names are. The follow script works. It just seems that once you have established the workbook as a library, you shouldn't have to close the library and use a different approach to access the spreadsheets. However, this does work quite well.
libname mylib pcfiles type=Excel path="\\server\excel.xlsx" ;
proc datasets library=mylib ; contents data=mylib._all_ out=work.sheetlist; run;
proc sql; create table work.sheets as select distinct compress(memname, "'") as sheet , compress(memname, "'$ -") as table from work.sheetlist where memname not contains "#Print"; ; quit;
%let nsheet=&sqlobs;
libname mylib clear;
%MACRO LOOP; %DO i = 1 %TO &nsheet;
data _NULL_; obs=&i ; set work.sheets point=obs; call symput ('insheet', trim(sheet)); call symput ('outfile', trim(table)); stop; run;
%put Now reading &i &insheet to &outfile;
proc import datafile = "\\server\excel.xlsx" dbms=xlsx out = work.&outfile replace; getnames=no; range= "&insheet.A1:B7"n ; run;
%END;
%MEND loop;
%LOOP;
... View more
