topic Re: How to get the recent file with specific keyword in SAS Programming

How to get the recent file with specific keyword

shasank — Tue, 29 Jun 2021 15:45:14 GMT

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.

Re: How to get the recent file with specific keyword

tarheel13 — Tue, 29 Jun 2021 15:52:40 GMT

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.

Re: How to get the recent file with specific keyword

shasank — Tue, 29 Jun 2021 15:59:40 GMT

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.

Re: How to get the recent file with specific keyword

tarheel13 — Tue, 29 Jun 2021 16:02:46 GMT

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

Re: How to get the recent file with specific keyword

shasank — Tue, 29 Jun 2021 16:06:14 GMT

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

Re: How to get the recent file with specific keyword

ChrisNZ — Tue, 29 Jun 2021 22:51:55 GMT

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

filename xlsdir pipe "dir ""&xlsdir.\*.xlsx"" /b /o:d /a:-d | find ""QR"" ";

Re: How to get the recent file with specific keyword

ChrisNZ — Tue, 29 Jun 2021 22:48:58 GMT

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

Re: How to get the recent file with specific keyword

ChrisNZ — Tue, 29 Jun 2021 23:06:22 GMT

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

Re: How to get the recent file with specific keyword

andreas_lds — Wed, 30 Jun 2021 05:59:10 GMT

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-example/ for details about executing ps from sas.