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
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.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.