DATA Step, Macro, Functions and more

Renaming the files in folder

Reply
Frequent Contributor
Posts: 127

Renaming the files in folder

I have several files that gets added to the folder daily.

For example:  test140429.xlsx, test140430.xlsx, test00502.xlsx,test00503.xlsx,

test00504.xlsx and so on..

In the above example test140429.xlsx (14 stands for year 2014, 04 -- April and  29 - day).   But May file I do not have year front of them for example test00502.xlsx, test00503.xlsx and so on.  I want to rename the files as test140502.xlsx, test140503.xlsx and so on.  I would appreciate if anybody can help me in this.

I am using following code to read the files in the directory.

%let src=c:\temp;

data srcdata ;

   infile "dir /b ""&src\"" " pipe truncover;   

  input fname $256. ;

  if index(fname,"test")  >  0;

run;

PROC Star
Posts: 7,467

Re: Renaming the files in folder

If you want help regarding how the SAS files will be named you'll also have to post the code you are using to import the Excel files.

Super User
Posts: 10,020

Re: Renaming the files in folder

Make a BAT file ,then execute it.

filename x pipe 'dir c:\temp\*.txt /b';
data x;
 infile x length=len;
 file 'c:\temp\rename.bat';
 input name : $varying40. len;
 first=scan(name,1,'.'); last=scan(name,-1,'.');
 if substr(name,anydigit(name),1)='0' then do;
  new=cat( cats('rename c:\temp\',name),' ',cats(scan(first,1,,'d'),'14',substr(first,length(first)-3),'.',last));
  put new;
 end;
run;
x 'c:\temp\rename.bat';
x 'del c:\temp\rename.bat';

Xia Keshan

Message was edited by: xia keshan

Message was edited by: xia keshan

Super User
Super User
Posts: 7,942

Re: Renaming the files in folder

Or replace the:

put new;

With:

call execute("x '"||strip(new)||"';");

No need for batch file then.

Frequent Contributor
Posts: 127

Re: Renaming the files in folder

Thanks a lot Ksharp.  It worked..

PROC Star
Posts: 7,467

Re: Renaming the files in folder

I can't test this at the moment, but would someone let me know if it would work as well?

%let dir=c:\temp\;

filename x pipe "dir &dir.*.xlsx /b";

data _null_;

  infile x length=len;

  input name : $varying40. len;

  if len=10 then do;

    oldname=catt("&dir.",name);

    newname=catt("&dir.",'14',substr(name,2));

    rc=rename(oldname, newname, 'file');

  end;

run;

Super User
Posts: 10,020

Re: Renaming the files in folder

Arthur.T,

Here is Log. And I have something for you, contact with you tomorrow.

NOTE: Numeric values have been converted to character values at the places given by: (Line)Smiley SadColumn).

      10:38

NOTE: Variable name is uninitialized.

NOTE: The infile X is:

      Unnamed Pipe Access Device,

      PROCESS=dir c:\temp\*.txt /b,RECFM=V,LRECL=256

NOTE: 3 records were read from the infile X.

      The minimum record length was 13.

      The maximum record length was 14.

NOTE: DATA statement used (Total process time):

      real time           1.14 seconds

      cpu time            0.07 seconds

PROC Star
Posts: 7,467

Re: Renaming the files in folder

: Thanks for testing it.  Unfortunately there was an error in my code.  Can (or someone) try it again with the following?

%let dir=c:\temp\;

filename x pipe "dir &dir.*.xlsx /b";

data _null_;

  infile x length=len;

  input name : $varying40. len;

  if len=10 then do;

    oldname=catt("&dir.",name);

    newname=catt("&dir.",'14',substr(name,2));

    rc=rename(oldname, newname, 'file');

  end;

run;

p.s., I corrected the code in my original post as well

Super Contributor
Posts: 1,636

Re: Renaming the files in folder

Hi Art,

log file from your code:

NOTE: SAS initialization used:

      real time           11.77 seconds

      cpu time            1.40 seconds

1    %let dir=c:\temp\;

2

3    filename x pipe "dir &dir.*.xlsx /b";

4

5    data _null_;

6

7      infile x length=len;

8

9      input name : $varying40. len;

10

11     if len=10 then do;

12

13       oldname=catt("&dir.",name);

14

15       newname=catt("&dir.",'14',substr(name,2));

16

17       rc=rename(oldname, newname, 'file');

18

19     end;

20

21   run;

NOTE: The infile X is:

      Unnamed Pipe Access Device,

      PROCESS=dir c:\temp\*.xlsx /b,RECFM=V,

      LRECL=256

NOTE: 3 records were read from the infile X.

      The minimum record length was 8.

      The maximum record length was 17.

NOTE: DATA statement used (Total process time):

      real time           0.40 seconds

      cpu time            0.03 seconds

PROC Star
Posts: 7,467

Re: Renaming the files in folder

Much appreciated!  And the files were renamed correctly?  If so, sure seems like a nice way to accomplish such tasks.

Super Contributor
Posts: 1,636

Re: Renaming the files in folder

Art,

To rename "test00502.xlsx" to "test140502.xlsx", I changed your code to:

%let dir=c:\temp\;

filename x pipe "dir &dir.*.xlsx /b";

data test;

  infile x length=len;

  input name : $varying40. len;

  if length(scan(name,1,'.'))=9 then do;

    oldname=catt("&dir.",name);

    newname=catt("&dir.test",'14',substr(name,6));

    rc=rename(oldname, newname, 'file');

  end;

run;

obs    name                               oldname                    newname            rc

   1     144est0050200000000.xlsx                                                          .
   2     Copy                                                                              .
   3     test00502.xlsx              c:\temp\test00502.xlsx    c:\temp\test140502.xlsx     0

Super Contributor
Posts: 275

Re: Renaming the files in folder

Also try this:

%let path=c:\temp\;

data _null_;

infile "dir/b/a:-d  ""&path"" " pipe truncover;

length ren $500.;

input name $20.;

newname=prxchange('s/test(14|0)/test14/',-1,name);

ren=catx(' ','rename',quote(cats("&path",name)),quote(newname));

infile ren pipe filevar=ren;

run;

Ask a Question
Discussion stats
  • 11 replies
  • 490 views
  • 0 likes
  • 6 in conversation