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;
/*
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;
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.
/*
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.