topic Re: Read Latest File From A Folder in SAS Programming

Read Latest File From A Folder

SteelersPitts — Thu, 12 Jul 2018 13:25:09 GMT

Hi All,

First off, I'm a Tableau professional finding his foot in SAS world. So if my questions are naive, I apologize in advance to the group.

I've a requirement where I have to read latest file from last from a folder. So the requirements is something like this:

Requirements:

1. Folder A stores files for sales as:

Sales_062018_06102018.txt

Sales_062018_06132018.txt

Sales_062018_06152018.txt

Sales_062018_06182018.txt

2. I've to go to the folder and pick the latest file (In this case Sales_062018_06182018.txt)

3. Any suggestions on how I should go and read the files and get the latest file? The number of files in the folder can be more than four.

Any pseudo code or code structure would be very helpful. Thank you

Re: Read Latest File From A Folder

Kurt_Bremser — Thu, 12 Jul 2018 13:42:57 GMT

You can make your life easier by orders of magnitude by using an intelligent format for the dates. MMDDYYYY is dumb, YYYYMMDD (as used in the ISO 8601 standard) is intelligent.

I'd then get the last file by doing that (UNIX):

filename oscmd pipe "ls &path./Sales_&month.&year._*.txt|tail -1";

data _null_;
infile oscmd truncover;
input filename $100.;
call symput('infile',filename);
run;

data want;
infile "&infile" .........

since UNIX ls orders automatically.

With your filenames, you need to work harder:

filename oscmd pipe "ls &path./Sales_&month.&year._*.txt";

data files;
infile oscmd truncover;
input filename $100.;
date = input(scan(scan(scan(filename,-1,'/'),1,'.'),3,'_'),mmddyy8.);
run;

proc sort data=files;
by descending date;
run;

data _null_;
set files;
call symput('infile',filename);
stop; *get only first obs;
run;

Edit: added the pipe engine in the filename statements.

Re: Read Latest File From A Folder

PaigeMiller — Thu, 12 Jul 2018 13:42:59 GMT

There are a lot of examples here in the SAS Communities of code to read the names of files in a specific folder. This is one such example: https://communities.sas.com/t5/Base-SAS-Programming/how-to-read-filenames-of-a-directory-into-a-file/td-p/71496. Once you have read the file names into a data set, then finding the one with the biggest date and time in the file name is simple.

Searching via your favorite search engine will find many more examples.

Re: Read Latest File From A Folder

SteelersPitts — Thu, 12 Jul 2018 13:56:19 GMT

Thank you for your valuable feedback. I appreciate it. I will code and let you know how it went.

Re: Read Latest File From A Folder

SteelersPitts — Thu, 12 Jul 2018 13:56:44 GMT

Thank you. Appreciate your feedback.