How to get the recent file with specific keyword

[shasank]

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. 

[tarheel13]

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.

[shasank]

Thank you for your response. That is what I was planning to do if that's the only way. But, wanted to ask the community for an efficient way to do this by adding one or two lines to the above code.

[tarheel13]

https://support.sas.com/kb/42/964.html 

[shasank]

Thanks Mate. This is similar to the code that I am using to get the last modified date code posted above in the question.

[ChrisNZ]

> 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"" ";

[ChrisNZ]

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')); 

[ChrisNZ · Accepted solution]

Also you should use call symputx() instead of cats() to trim trailing spaces.

[andreas_lds]

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.