topic Re: Importing Excel Files in a folder in New SAS User

Importing Excel Files in a folder

RADAGBE9 — Mon, 08 Feb 2021 16:48:45 GMT

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

Re: Importing Excel Files in a folder

Tom — Mon, 08 Feb 2021 17:29:12 GMT

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;

Re: Importing Excel Files in a folder

SASJedi — Wed, 10 Feb 2021 14:11:10 GMT

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