The %xls2sas macro from the article referenced above is INCOMPLETE. The authors explain, amongst other things, that they make use of SAS functions such as DREAD. However, the macro itself does not have this line of code. Here is the %macro as I was trying to test it. Perhaps someone can tell me where I am wrong or what I am missing. %macro dt_xls2sas (folder= ,subfd=Y ,exclfd= ,startrow=); *- Local macro variable declaration; %local _j _cnt _dsid _i _num _s_ext _s_name _filename _rc; %let _rc=%qsysfunc(filename(filrf,&folder)); %if &_rc=0 %then %do; *- Open the folders; %let _dsid=%sysfunc(dopen(&filrf)); %if &_dsid>0 %then %do; *- Ge the number of folders; %let _num=%sysfunc(dnum(&_dsid)); %do _i=1 %to &_num; %let _filename=%sysfunc(dread(&_dsid.,&_i.)); %put _filename=; *- Check if the file extension is XLS. If so, open Excel workbook and read in the multi-sheets or named ranges; %if %upcase(&_s_ext)=XLS %then %do; libname excellib excel "&folder\&_filename"; libname excellib clear; %end; *- If the file extension resolves to NULL, check if the read subfolder flag is Y and also it is not; *- in the exclusion folder list. If so, call %xls2sas to open the subfolder and Excel files under it; %end; %end; %end; %else %if %scan(&_filename, 2, .)= and &subfd=Y and %qsysfunc(indexw(&exclfd,&_filename))=0 %then %do; %dt_xls2sas(folder=&folder\&_filename, subfd=&subfd, exclfd=&exclfd, startrow=&startrow) %end; %mend dt_xls2sas;
... View more