Desktop productivity for business analysts and programmers

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

Reply
Occasional Contributor
Posts: 16

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

Super User
Posts: 10,557

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

Posted in reply to srinivaschary

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
How to convert datasets to data steps
How to post code
Super User
Posts: 10,557

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

Posted in reply to srinivaschary

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
How to convert datasets to data steps
How to post code
Ask a Question
Discussion stats
  • 2 replies
  • 704 views
  • 0 likes
  • 2 in conversation