06-29-2017 02:01 AM
Eg: excel files(
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.
06-29-2017 02:25 AM
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.
06-29-2017 02:33 AM
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.