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

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

srinivaschary — Thu, 29 Jun 2017 06:01:12 GMT

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

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

Kurt_Bremser — Thu, 29 Jun 2017 06:25:12 GMT

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.

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

Kurt_Bremser — Thu, 29 Jun 2017 06:33:56 GMT

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.