How to import most recent excel file (from folder) in to SAS EG

[srinivaschary]

Hi,

Eg: excel files(

exel28062017_060000.XLSX

exel28062017_050000.XLSX

exel28062017_070000.XLSX) in a folder ,which is located in shares path.

i would like to pick and import most recent (exel28062017_070000.XLSX) file from folder .

Kindly help me on importing the recent excel file.

Thanks

[Kurt_Bremser]

Start at the root of your problem and use a proper date format in the Excel filename.

Using a ddmmyy format instead of yymmdd is simply dumb and causes unnecessary work.

Then you would simply take the last line of an ordered list, and that's it.

Maxim 33: Intelligent data makes for intelligent programs.

As it is, you will have to jump through some loops:

/* Windows */
filename in pipe "dir &path. /b";
/* UNIX */
filename in pipe "cd &path.; ls";

data files;
infile in truncover;
input file_name $50.;
if upcase(substr(file_name,1,4)) = 'EXEL' and upcase(scan(file_name,2,'.')) = 'XLSX';
file_date = input(substr(file_name,5,8),ddmmyy8.);
run;

proc sort data=files;
by file_date descending file_name descending; /* by file_name to keep the order of times */
run;

data _null_;
set files;
if _n_ = 1 then call symput('excelfile',trim(file_name));
stop;
run;

Now you have your required filename in the macro variable &excelfile.

[Kurt_Bremser]

Just to illustrate the usefulness of correctly used date formats in filenames (or any kind of list):

filename in "cd &path.; ls|tail -1";

data _null_;
infile in truncover;
input file_name $50.;
call symput('excelfile',trim(file_name));
run;

gives you the most recent file on UNIX.