BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
RADAGBE9
Fluorite | Level 6

I have Excel Files in a folder as shown below. I need a sas code that will go into the folder and import the latest file with name starting "xlfile". In other words I would like to import only "xlfile.01.05.2021.xls" from the list of files. This code will be run everyday so it should import the latest file each day.

 

xlfile.01.02.2021.xls

xlfile.01.03.2021.xls

xlfile.01.04.2021.xls

xlfile.01.05.2021.xls

otherfiles1.xls

otherfiles2.xls

etc

 

 

 

I would like to a code that will go and import only the latest 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

So you need a method to get the list of files in the directory.  Then a method to filter to the names of interest. Then a way to tease out the date from the filename. Then a way to find the latest (largest date).  And finally a way to import the selected file.

 

The easiest way to do the first three is if you can use operating system commands.  So DIR (on Windows) or ls (on unix) will return a list of files.  So on Unix you might do something like this (assuming 01.05 means January fifth).

%let path=/name/or/your/directory;
data files ;
  infile "cd &path ; ls xlfile.*.xls" pipe truncover ;
  input filename $256. ;
  date = input(substr(filename,8,mmddyy10.);
  format date yymmdd10.;
run;
  

Now just sort by the new DATE variable and take the last name and put it into a macro variable.

proc sort data=files;
  by date;
run;
data _null_;
  set files end=eof;
  if eof then call symputx('filename',filename);
run;

Now you can use the macro variable in whatever code you were using to IMPORT the XLS file.

proc import out=want replace 
  datafile="&path/&filename" dbms=xls
;
run;

 

View solution in original post

2 REPLIES 2
Tom
Super User Tom
Super User

So you need a method to get the list of files in the directory.  Then a method to filter to the names of interest. Then a way to tease out the date from the filename. Then a way to find the latest (largest date).  And finally a way to import the selected file.

 

The easiest way to do the first three is if you can use operating system commands.  So DIR (on Windows) or ls (on unix) will return a list of files.  So on Unix you might do something like this (assuming 01.05 means January fifth).

%let path=/name/or/your/directory;
data files ;
  infile "cd &path ; ls xlfile.*.xls" pipe truncover ;
  input filename $256. ;
  date = input(substr(filename,8,mmddyy10.);
  format date yymmdd10.;
run;
  

Now just sort by the new DATE variable and take the last name and put it into a macro variable.

proc sort data=files;
  by date;
run;
data _null_;
  set files end=eof;
  if eof then call symputx('filename',filename);
run;

Now you can use the macro variable in whatever code you were using to IMPORT the XLS file.

proc import out=want replace 
  datafile="&path/&filename" dbms=xls
;
run;

 

SASJedi
SAS Super FREQ

The method described by @Tom is exactly what you need when running SAS from a PC SAS installation. But if you are working in a company, often you will have a SAS client-server setup, where you use Enterprise Guide or SAS Studio to write and submit code that actually runs on a SAS compute server located on a different machine. Setting the SAS invocation option NOXCMD is the default in those situations, and you won't be able to use FILENAME PIPE, X, SYSEXEC, or other code that executes arbitrary O/S commands on the compute server.  And most system administrators will be more comfortable leaving that setting as-is.

To work with file system files and directories in those cases, consider using the base SAS functions designed for that (FILENAME, DOPEN, DREAD, DCLOSE, FOPEN, FOPTNAME, FREAD, FCLOSE, etc.). These can be executed even with NOXCMD in effect, and as an added bonus, don't require you to know what O/S underlies your SAS session or to be familiar with DOS or Linux syntax. You can experiment with those yourself, or you can try out the macros I personally use for jobs like this. I've shared these macros on Github (https://github.com/SASJedi/sas-macros).

If your SAS can access internet assets, you can use this SAS code to compile the macros in your SAS session:

/* Get the necessary macro files rom GitHub */
filename getmacro url "https://raw.githubusercontent.com/SASJedi/sas-macros/master/findfiles.sas";
%include getmacro;
filename m1 url "https://raw.githubusercontent.com/SASJedi/sas-macros/master/exist.sas";
%include getmacro;
filename getmacro url "https://raw.githubusercontent.com/SASJedi/sas-macros/master/translate.sas";
%include getmacro;
filename getmacro url "https://raw.githubusercontent.com/SASJedi/sas-macros/master/fileattribs.sas";
%include getmacro;
filename getmacro;

Once you have the macros, you can ask for syntax help using a question mark, like this:

%findFiles(?)

And to use this macro to solve the problem you presented, I'd do something like this:

/* Put the path to your Excel file folder here: */

%let path=    ;
%findFiles(&path,xls,work.ExcelFiles)

proc sql noprint;
select filename 
   into:thisfile 
   from work.ExcelFiles
   where lowcase(filename) like 'xlfile.%'
   order by input(substr(filename,8,10),mmddyy10.) desc
;
quit;

PROC IMPORT OUT=WANT
            DATAFILE= "&path\&thisfile" 
            DBMS=EXCEL REPLACE;
RUN;

May the SAS be with you!

Mark

Check out my Jedi SAS Tricks for SAS Users

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 3539 views
  • 0 likes
  • 3 in conversation