topic Re: Combine multiple Excel files containing specific text in their file names into a single SAS tabl in SAS Programming

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

avz — Fri, 04 Jun 2021 14:22:30 GMT

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.

Re: Combine multiple Excel files containing specific text in their file names into a single SAS tabl

PhilC — Fri, 04 Jun 2021 15:14:01 GMT

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 /?

Re: Combine multiple Excel files containing specific text in their file names into a single SAS tabl

Reeza — Fri, 04 Jun 2021 15:20:20 GMT

%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/p00ab6ey29t2i8n1ihel88tqtga9.htm

Only import if the file is XLSX.

Re: Combine multiple Excel files containing specific text in their file names into a single SAS tabl

Tom — Fri, 04 Jun 2021 15:32:41 GMT

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.

Re: Combine multiple Excel files containing specific text in their file names into a single SAS tabl

avz — Fri, 04 Jun 2021 17:10:04 GMT

Thank you, Reeza.