Hi Cohort,
I have looked through the community's old responses for answers but couldn't find the one that I could not tailor for this requirement.
Here is the code I am using in windows to get the last modified file in windows.
%* identify the most recent response file;
%let xlsdir=C:\filedump;
filename xlsdir pipe "dir ""&xlsdir.\*.xlsx"" /b /o:d /a:-d";
data _null_; infile xlsdir truncover end=eof; input fname $255.; if eof then call symput('xlsfile',cats(fname)); run;
%put &xlsfile.;
What I need is to get the file with max date as suffix with specific keyword - QR - There are other files in the same folder with other keywords.
File name formats:
FN_QR_Report_20210423.xlsx
EN_QR_Report_20210513.xlsx
TN_QR_Report_20210520.xlsx
I need TN_QR_Report_20210520.xlsx to be assigned to &xlsfile.
Thank you for your time.
Also you should use call symputx() instead of cats() to trim trailing spaces.
Can you get the filename in a variable and then extract the date and convert it to a SAS date? Store that in a variable and that sort by the date and then you can get the latest date.
> What I need is to get the file with max date as suffix with specific keyword
I see you sort the DIR results. Do you need the max date from the OS or from the file suffix?
If you need by suffix as stated, there is no need to sort when running DIR, but you must sort when you have the file list.
>What I need is to get the file with max date as suffix with specific keyword - QR
Why not keep just these files, then you code would work as is. Maybe
filename xlsdir pipe "dir ""&xlsdir.\*QR*.xlsx"" /b /o:d /a:-d";
or
filename xlsdir pipe "dir ""&xlsdir.\*.xlsx"" /b /o:d /a:-d | find ""QR"" ";
Note that you can sort and assign in one step. For example:
proc sql;
select FNAME into :xlsfile from FILES having compress(FNAME,,'dk')=max(compress(FNAME,,'dk'));
Also you should use call symputx() instead of cats() to trim trailing spaces.
If executing Powershell code is allowed, you could use
Get-ChildItem *QR*.xlsx | Sort-Object LastWriteTime -Descending | Select-Object -First 1 FullName
to get the latest file.
See https://blogs.sas.com/content/sasdummy/2011/09/15/calling-windows-powershell-from-sas-a-simple-examp... for details about executing ps from sas.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.