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;
... View more