Combine multiple Excel files containing specific text in their file names into a single SAS table

[avz]

I found a helpful macro function to import multiple Excel files and combine them into a single SAS table from https://www.listendata.com/2013/10/sas-importing-multiple-excel-files-in.html

%macro MultImp(dir=,out=);

%let rc=%str(%'dir %")&dir.%str(\%" /A-D/B/ON%');
filename myfiles pipe %unquote(&rc);

data list;
length fname $256.;
infile myfiles truncover;
input myfiles $100.;

fname=quote(upcase(cats("&dir",'\',myfiles)));
out="&out";
drop myfiles;
call execute('
  proc import dbms=xlsx out= _test
            datafile= '||fname||' replace ;
  run;
  proc append data=_test base='||out||' force; run;
  proc delete data=_test; run;
');
run;
filename myfiles clear;

%mend;

%MultImp(dir=G:\MY DRIVE\Folder\test,out=merged);

 However, if the folder contains something other than an Excel file, the import fails:

ERROR: Error opening XLSX file -> G:\MY DRIVE\Folder\desktop.ini.xlsx .  It is either not an 
Excel spreadsheet or it is damaged.   Error code=8014900A
Requested Input File Is Invalid
ERROR: Import unsuccessful.  See SAS Log for details.

How can I ensure that the function only attempts to import .xlsx files containing a specific text string in the filename? 

Thanks for the help. 

[PhilC]

This is technically a DOS question.  Run these lines so you can see the RC macro variable.

%let dir=MYfiles;
%let rc=%str(%'dir %")&dir.%str(\%" /A-D/B/ON%');

%put NOTE: &=rc;

and run this line in a DOS shell to see how to use the DOS command that you are using.

dir /?

[Reeza · Accepted solution]

%macro MultImp(dir=,out=);

%let rc=%str(%'dir %")&dir.%str(\%" /A-D/B/ON%');
filename myfiles pipe %unquote(&rc);

data list;
length fname $256.;
infile myfiles truncover;
input myfiles $100.;

fname=quote(upcase(cats("&dir",'\',myfiles)));
out="&out";
drop myfiles;

if find(fname, "xlsx", 'it') then 
call execute('
  proc import dbms=xlsx out= _test
            datafile= '||fname||' replace ;
  run;
  proc append data=_test base='||out||' force; run;
  proc delete data=_test; run;
');
run;
filename myfiles clear;

%mend;

%MultImp(dir=G:\MY DRIVE\Folder\test,out=merged);

https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lefunctionsref/p00ab6ey29t2i8n1ihel88tqtga...

Only import if the file is XLSX.

[avz]

Thank you, Reeza.

[Tom]

Only keep the XLSX files in your list.  For example by adding a subsetting IF statement.

  if lowcase(scan(myfiles,-1,'.')='xlsx';

Updated macro.

%macro MultImp(dir=,out=);

filename myfiles pipe %bquote('dir "&dir\" /A-D/B/ON');

data list;
  length fname $256 ;
  infile myfiles truncover;
  input myfiles $100.;
  if lowcase(scan(myfiles,-1,'.')='xlsx';
  fname=quote(cats("&dir",'\',myfiles));
  out="&out";
  drop myfiles;
  call execute(catx(' '
    ,'proc import dbms=xlsx out= _test datafile=',fname,'replace;run;'
    ,'proc append data=_test base=',out,'force;run;'
    ,'proc delete data=_test; run;'
  ));
run;

filename myfiles clear;

%mend;

%MultImp(dir=G:\MY DRIVE\Folder\test,out=merged);

Note make sure you have complete control over how the data is entered into those XLSX sheets.  Otherwise your PROC APPEND step will fail when variables had the wrong type on some of the XLSX files.