How to Rename External Excel Files with Conditions

[peishanxin]

Hi all,

I'm trying to rename some external files saved in a specific folder. I'd like to rename the old file name to 'A' when it contains '214', rename it to 'B' when it contains '217', etc.

Below is my code, I kept getting the "Error renaming:" error. Can someone take a look and correct it plz? thanks!!!

%let folder = /report/Sep2024;
filename mydir "&folder.";

data _null_;
length old_name $300 new_name $300;
did = dopen('mydir'); 

if did > 0 then do;
    do i = 1 to dnum(did);

        file_name = dread(did, i);

        old_name = "&folder./" || file_name; 

        new_name = "";

        if find(file_name, '214') > 0 then new_name = "&folder./A.xlsx"; 
        else if find(file_name, '217') > 0 then new_name = "&folder./B.xlsx"; 
        else if find(file_name, '445') > 0 then new_name = "&folder./C.xlsx"; 
        else if find(file_name, '311') > 0 then new_name = "&folder./D.xlsx"; 

        if new_name ne "" then do;
            
    rc = rename("old_name","new_name",'file');
         
            if rc = 0 then put "Successfully renamed: " old_name " to " new_name;
            else put "Error renaming: " old_name " to " new_name;
        end;
    end;
    rc = dclose(did); 
end;
run;

[ballardw]

Any error you should send the LOG with the submitted code and all the notes as well as the text of the error. Copy the text and paste into a text box on the forum.

If the code involves a macro or macro variables you should set OPTIONS MPRINT SYMBOLGEN; before executing so we can see the generated code.

Is "/report" a disk mount point? If you are getting errors about file not found or such that may be because SAS is looking for folder relative to a different location than you think, such where SAS executes and not the drive holding that folder.

This is not going to use the macro  variable to find the directory:

did = dopen('mydir'); 

That looks for directory named mydir. To use the macro variable the reference must start with & and to resolve must be between double quotes .

did = dopen("&mydir"); 

Also, since you have the new and old names in variables then should not be quoted in the rename function call.

 rename("old_name","new_name",'file');

would be looking for an actual file named old_name and try to name it new_name and not use the variables.

[Patrick · Accepted solution]

I can execute your code as is successfully after implementing one change.

Replace line of code....

rc = rename("old_name","new_name",'file');

with:

rc = rename(strip(old_name), strip(new_name), 'file');

WARNING

With your current code logic and if you've got more than one Excel file with for example 214 in the name and under the same folder then all the matching files will get renamed to target file A.xlsx. With multiple renames A.xlsx will get overwritten and in the end contain the data from the source Excel that came last (in the sort order). All the other Excel files will be wiped out.