SAS Macro to Check Each Subfolder Within a Directory and Import Excel Files

[nbwest76]

I'm trying to put together a SAS macro that will check each subfolder within a directory and import the Excel spreadsheets if they exist.

The directory path is structured by the current month name and year. I am able to define that with the %curr_month_path macro variables below, so I'm good there.

%let dt = today();
%let folder_path = 'c:\My Folder';
%let curr_month_name = strip(put(&dt, monname.));
%let curr_month_year = year(&dt);
%let curr_month_path = catx('\', &folder_path, catx(' ', &curr_month_name, &curr_month_year));

Within the monthly folder/directory, there are individual subfolders for each day of the month example: "4 1 2023", "4 2 2023" etc. and within each of the daily folders there are a possibility of containing two Excel files.

I'd like to loop through each of the subfolders to check to see if a an .xlsx file that starts with the word "Invoice" exists and then import that file.

At it's simplest form, I do have a macro below that will import one of the spreadsheets for reference, I would just need to dynamically check each folder to import the files.

%macro nw;
   proc import out=t_import(rename=("Source Case #"n=case_number) keep="Source Case #"n)
   datafile="c:\My Folder\Invoice File 123.xlsx"
   dbms=xlsx
   replace;
 run;
%mend nw;

[Reeza]

Here's a macro that will scan a directory (and subdirectories) and list the files. Use that instead to list all the files and then you have the path and filenames. 

Parse that output to get a list of files you want to import. 

Then you can import each file using the macro you wrote or dynamically using call execute, or a combination. 

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

Probably more efficient if this isn't actually all under My Folder and in a separate directory where you'll have less false positives. 

[Ksharp · Accepted solution]

/*
Assuming you are using Windows OS
and could invoke OS command via SAS.
*/
%let dir=  c:\temp  ;




options validmemname=extend;
filename x pipe %sysfunc(quote(dir "&dir\*.xlsx" /s /b));
data _null_;
infile x ;
input ;
call execute(catt('proc import datafile="',_infile_,'" 
 out=',compress(scan(_infile_,-2,'\.'),,'kda'),' dbms=xlsx replace;run;' ));
run;

[nbwest76]

Exactly what I was looking for, thanks!