SAS program to check file level controls on multiple files

Question

This code is working perfectly when we pass single file but I need to achieve the same when I pass multiple files. If any file fails any macro condition it should be logged in the error.csv.

Here I do have 2 macros, whenever I pass a file it should go through the 2 macros and if encounters any issues it should write the error msg in the errorlog.csv.

Macro 1: get_FileExtension - used to check whether the file which is passed have valid extensions(TXT, TTX, SAS7BDAT, CSV). IF the passed file has no valid extension it writes log in errorlog.csv ("Invalid file extension for file &file")

Macro 2: check_file_exists - used to check whether the file which is passed is on the server. IF the passed file is not on the server it writes log in errorlog.csv ("The file &filepath does not exist on the server")

%macro get_FileExtension(file, path);
    %local extension logtext;
    %let extension = %upcase(%sysfunc(scan(&file.,-1,.)));
    
    %if &extension. = TXT or &extension. = TTX %then %let extension='09'x;
    %else %if &extension. = CSV %then %let extension='CSV';
    %else %if &extension. = SAS7BDAT %then %let extension='SAS';
    %else %do;
        %let logtext = ERROR: Invalid file extension for file &file.;
        %put &logtext;
        %global error_message;
        %let error_message = &logtext;
        data null;
            file errorlog mod;
            /* Check if headers have been written */
            if n = 1 then put "Date Time Message";
            put "&sysdate9. &sysampm. &logtext";
        run;
        %let extension='UNKNOWN';
    %end;
    
    &extension.
%mend get_FileExtension;

%macro check_file_exists(filepath);
    %local logtext;
    %if %sysfunc(fileexist(&filepath.)) %then %do;
        %let logtext = NOTE: The file &filepath exists on the server.;
        %put &logtext;
    %end;
    %else %do;
        %let logtext = ERROR: The file &filepath does not exist on the server.;
        %put &logtext;
        %global error_message;
        %let error_message = &error_message. %str( ) &logtext;
        data null;
            
            /* Check if headers have been written */
            if n = 1 then put "Date Time Message";
            put "&sysdate9. &sysampm. &logtext";
        run;
    %end;
%mend check_file_exists;

%macro check_and_get_FileExtension(file, path);
    %local extension;
    %let extension = %get_FileExtension(&file, &path);
    %check_file_exists(&file);
    %put NOTE: File Extension is &extension.;
    %put &error_message.;
    data null;
        file "//home/ketha/errorlog.csv" mod;
        length message $200; /* Adjust the length as per your requirement */
        message = catx(' ', "&sysdate9.", "&sysampm.", "&error_message.");
        put message;
    run;
%mend check_and_get_FileExtension;

%let file=/home/ketha/eoth_bshg.invalidext;
%let path=/home/ketha;
%check_and_get_FileExtension(&file., &path.);

Output for invalid file:

ERROR: Invalid file extension for file /home/ketha/eoth_bshg.invalidext
ERROR: The file /home/ketha/eoth_bshg.invalidext does not exist on the server.

I tried the same above for multiple files but it is failing. I need help to achieve the same on multiple files.

Answer 1

A lazy but efficient approach would be to write another macro to iterate over your list of paths/files. Of note, you'll want to take care how they are delimited (e.g. can your files/paths contain spaces?). A simple example:

%macro multi_check_and_get(files, paths, delim = @);
   %do i = 1 %to %sysfunc(countw(&files, &delim));
      %check_and_get_FileExtension(%scan(&files, &i, &delim), %scan(&paths, &i, &delim));
   %end;
%mend;

There is a lot I would extend here, for example the code assumes that files and paths contain an equal number of delim-delimited values, there's zero consideration for macro quoting, this will do nothing if files is blank, etc.

Answer 2

If you want to pass a list of values then pick a delimiter to use, hopefully something that cannot appear in one of the values. | is a good choice for filenames since it cannot be part of the filename.

 %macro check(filelist,path);
   %local i file ext exists ;
   %do i=1 %to %sysfunc(countw(&filelist,|));
     %let file=%qscan(&filelist,&i,|);
     %if not %index(&file,.) %then %let ext=;
     %else %let ext=%qscan(&file,-1,.);
     %let exist=%sysfunc(fileexist(&path.&file));
%* Now do something with the derived information ;
     %put &=i &=exist &=ext &=file ;

   %end;
%mend;

Example call:

1    %check(filelist=bar.txt|class.xlsx|class.sas7bdat|junk|junk.junk,path=c:\downloads\);
I=1 EXIST=1 EXT=txt FILE=bar.txt
I=2 EXIST=1 EXT=xlsx FILE=class.xlsx
I=3 EXIST=1 EXT=sas7bdat FILE=class.sas7bdat
I=4 EXIST=0 EXT= FILE=junk
I=5 EXIST=0 EXT=junk FILE=junk.junk