SAS Rename of file with open parentheses

Question

I have a large number of Excel files to be processed. I've created code that reads their names into a dataset for execution of each file. Each of the files, unfortunately, has an open left parentheses like "(", such as: Jason (Allen.xlsx

I've written code to clean up the names in my listing data set, but I can't seem to get the RENAME based macro to work with the open parentheses. Using two dummy Excel files, the first record in the school_file_list dataset will change names just fine because it has closed parentheses...but the second file name will not change. Is there a way to edit my macro to feed it the file names that have the offending open parentheses?

data school_file_list;
    input fname $ 1-18 newfname $ 20-42;
    cards;
Jason (Allen).xlsx Jason Allen.xlsx
Jason (A.xlsx      Jason A.xlsx
;
run; 

%let filepath= %nrbquote(C:\mypath\);

%macro file_rename(from, to);
data _null_;
    rc=rename("&filepath.&from", "&filepath.&to", "file");
    if rc ne 0 then do;
        put "Rename of file &from to &to DID NOT WORK!!!" rc=;
        call symput('err','1');
    end;
    else put "Rename of file &from to &to WORKED.";
    run;
%mend file_rename;

data _null_; set school_file_list;
    call execute ('%file_rename(from='||strip(fname)||', to='||strip(newfname)||');');
    run;

Answer 1

It seems to me the issue is the macro and not the names or the renaming. Not sure what value the macro is adding in this process. Why not just issue the rename statements directly in the data step?

data _null_;
  set school_file_list;
  rc=rename(cats("&filepath",fname),cats("&filepath",newfname),"file");
...

If you need to make a macro then make one that takes the name of the dataset as the input.

Something like this:

%macro file_rename(dsname,from=from,to=to,filepath=" ");
data _null_;
  set &dsname;
  rc=rename(cats(&filepath,&from),cats(&filepath,&to),"file");
  if rc ne 0 then do;
     put "Rename of file " &from "to " &to "in " &filepath "DID NOT WORK!!!" rc=;
     call symput('err','1');
  end;
  else put "Rename of file " &from "to " &to "in " &filepath "WORKED.";
run;
%mend file_rename;

Which for your example dataset and root path you could call like this:

%file_rename
(dsname=school_file_list
,from=fname
,to=newfname
,filepath="&filepath"
);

Which will generate code like:

894  %let filepath=c:\;
895  %file_rename(dsname=school_file_list,from=fname,to=newfname,filepath="&filepath");
MPRINT(FILE_RENAME):   data _null_;
MPRINT(FILE_RENAME):   set school_file_list;
MPRINT(FILE_RENAME):   rc=rename(cats("c:\",fname),cats("c:\",newfname),"file");
MPRINT(FILE_RENAME):   if rc ne 0 then do;
MPRINT(FILE_RENAME):   put "Rename of file " fname "to " newfname "in " "c:\" "DID NOT WORK!!!" rc=;
MPRINT(FILE_RENAME):   call symput('err','1');
MPRINT(FILE_RENAME):   end;
MPRINT(FILE_RENAME):   else put "Rename of file " fname "to " newfname "in " "c:\" "WORKED.";
MPRINT(FILE_RENAME):   run;

With a minor change

%if %length(&dsname) %then %do;
  set &dsname;
%end; 

you could also make the macro work without a dataset.

%file_rename
(from='Jason (Allen).xlsx'
,to='Jason Allen.xlsx'
,filepath="&filepath"
);