First step it to get the list of files into a dataset. Since you have multiple directories you need something that can handle that.
For example you could use this macro: https://github.com/sasutils/macros/blob/master/dirtree.sas
%dirtree(C:\users\manohar\downloads\data,out=files)
Which will create a dataset with these variables.
Output dataset structure
--NAME-- Len Format Description
FILENAME $256 Name of file in directory
TYPE $1 File or Directory? (F/D)
SIZE 8 COMMA20. Filesize in bytes
DATE 4 YYMMDD10. Date file last modified
TIME 4 TOD8. Time of day file last modified
DEPTH 3 Tree depth
PATH $256 Directory name
So say you end up with this dataset:
data files;
infile cards dsd ;
input filename :$256. path :$256. ;
cards;
trans 260423.xls,C:\users\manohar\downloads\data\02may2023
trans 270423.xls,C:\users\manohar\downloads\data\03may 2023
;
You could then try to convert the directory name into an actual date.
It also looks like the XLS filename might also have a date value in DDMMYY style, although perhaps not.
data xls_files;
set files;
if upcase(scan(filename,-1,'.')) = 'XLS' ;
dirdate = input(scan(path,-1,'\'),date11.);
trandate = input(scan(filename,-2,' .'),ddmmyy.);
format dirdate trandate yymmdd10.;
run;
So now you have this:
Obs filename path dirdate trandate
1 trans 260423.xls C:\users\manohar\downloads\data\02may2023 2023-05-02 2023-04-26
2 trans 270423.xls C:\users\manohar\downloads\data\03may 2023 2023-05-03 2023-04-27
Now you can filter by one of those calculated DATE values to find the files that represent the date you want.
And then use that list to generate the code to convert the XLS sheets into SAS datasets.
filename code temp;
data _null_;
file code;
set xls_files;
where trandate >= '01JAN2018'd ;
put 'proc import file="' path +(-1) '\' filename +(-1) '" dbms=xls'
' out=trans_' trandate yymmddn8. ' replace;'
'run;'
;
run;
%include code / source2;
Example:
529 +proc import file="C:\users\manohar\downloads\data\02may2023\trans 260423.xls" dbms=xls out=trans_20230426 replace;
529!+ run;
530 +proc import file="C:\users\manohar\downloads\data\03may 2023\trans 270423.xls" dbms=xls out=trans_20230427 replace;
530!+ run;
