topic PROC IMPORT MACRO Importing multiple files - file name begins with "Test" in SAS Programming

PROC IMPORT MACRO Importing multiple files - file name begins with "Test"

pappusrini — Thu, 12 Jan 2023 03:47:04 GMT

Good Evening All, please assist in the following scenario as i'm trying to import multiple EXCEL files using proc Import macro and file names begin with "test". Can I apply this wild card test% in the MEND statement so that all the files that begin with test will be imported. Please see example im trying to use

%macro Import_file(VENDORFILE);
PROC IMPORT
DATAFILE= "File directory location"
RUN;
%MEND test%;

Thank you all in advance.

Re: PROC IMPORT MACRO Importing multiple files - file name begins with "Test"

Tom — Thu, 12 Jan 2023 04:02:56 GMT

@pappusrini wrote:

Good Evening All, please assist in the following scenario as i'm trying to import multiple EXCEL files using proc Import macro and file names begin with "test". Can I apply this wild card test% in the MEND statement so that all the files that begin with test will be imported. Please see example im trying to use

%macro Import_file(VENDORFILE);
PROC IMPORT
DATAFILE= "File directory location"
RUN;
%MEND test%;

Thank you all in advance.

No. That does not make any sense at all. The only text you should have on the %MEND statement is the same name as you ahve on the %MACRO statement. If you put something different there then SAS will write a note in the log to let you know that you probably have either made a typo or messed up something else that is causing it to skip one of the %MACRO or %MEND statements.

If you want to import multiple physical XLSX files you will need run multiple steps.

proc import dbms=xlsx file="somefile.xlsx" out=somedsname replace;
run;
proc import dbms=xlsx file="otherfile.xlsx" out=otherdsname replace;
run;
...

If you want to import multiple sheets from a single XLSX file it might be easier to use the XLSX libref engine instead of PROC IMPORT.

libname in xlsx "something.xlsx";
libname out "some directory";
proc copy inlib=in outlib=out;
run;

If you have a macro that can import one XLSX file and a dataset with the list of XLSX files to import you can use a data step to CALL the macro multiple times, each time passing in a different filename. Note the macro you showed is NOT a working macro to import an XLSX file. But assuming you did have a macro named %IMPORT_FILE() that did work, then the data step might look like this:

data _null_;
  set filelist;
  where upcase(filename) like 'TEST%.XLSX';
  call execute(cats('%nrstr(%import_file)(',filename,')'));
run;

Re: PROC IMPORT MACRO Importing multiple files - file name begins with "Test"

pappusrini — Thu, 12 Jan 2023 04:13:53 GMT

Thanks Tom… I’m trying to import multiple files from the folder. After the %Mend statement if I call the macro name along with the file names it will run. Here is the example:

%mend;
%Macroname (testfile1);
%Macroname (testfile2); etc

My question is rather than typing these file names manually can I bring all the files at once using wildcard test%

Sorry if I’m confusing you with my question.

Re: PROC IMPORT MACRO Importing multiple files - file name begins with "Test"

Tom — Thu, 12 Jan 2023 05:18:02 GMT

So your question is not about how to run the macro. It its about how to discover the names of the files.

That question has been answered many times on this list.

The easiest is to just ask your operating system to tell you names of the files.

For example if your SAS program is running on Unix just use the ls command.

data files ;
  infile "ls /mydirectoryname/test*.xlsx" pipe truncover;
  input filename $256. ;
run;

Now you have the data you need to run the data step I showed you before to call the macro multiple times.

If your system admins have disabled the ability for you to run operating system commands then you will need to work harder.

So use something like this macro to get the list of files:

https://github.com/sasutils/macros/blob/master/dirtree.sas