<rant>Why do some ******, ****-******* ****** always come up with the stupid idea of using ANYTHING BUT a YMD date in filenames?</rant>
Anyway, here's some code that extracts the date from filenames, converts it to a SAS date value and gets the max of it:
%let indir=C:\user;
data _null_;
length dref $8 name $200;
rc = filename(dref,"&indir.");
did = dopen(dref);
if did
then do;
do i = 1 to dnum(did);
name = dread(did,i);
if scan(name,2,"_") = "excel" and scan(name,3,"_") = "name" and scan(name,-1,".") = "xlsx"
then do;
date = input(scan(name,4,"_"),ddmmyy8.);
if date > maxdate
then do;
maxdate = date;
maxnum = scan(name,1,"_");
end;
end;
end;
if maxdate ne .
then call symputx("infile",catx("_",maxnum,"excel","name",put(maxdate,ddmmyyn8.))!!".xlsx");
else call symputx("infile","not found");
rc = dclose(did);
end;
else call symputx("infile","directory not found");
rc = filename(dref);
run;
With a properly structured filename, where the date comes before any variable part and has a date in YMD order, it's one simple one-liner:
(UNIX example)
data _null_;
infile "ls &indir./excel_name_*_*.xlsx|tail -1" pipe;
input;
call symputx("infile",_infile_);
run;
Maxim 33: Intelligent Data Makes for Intelligent Programs.
Filenames are data, and properly structured filenames makes handling them a breeze.
... View more
