BookmarkSubscribeRSS Feed
srinivaschary
Calcite | Level 5

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

2 REPLIES 2
Kurt_Bremser
Super User

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.

Kurt_Bremser
Super User

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 5041 views
  • 1 like
  • 2 in conversation