Desktop productivity for business analysts and programmers

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

Reply
Occasional Contributor
Posts: 13

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

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

Esteemed Advisor
Posts: 6,646

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Esteemed Advisor
Posts: 6,646

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 2 replies
  • 105 views
  • 0 likes
  • 2 in conversation