topic Re: Renaming the files in folder in SAS Programming

Renaming the files in folder

pp2014 — Tue, 13 May 2014 19:23:34 GMT

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;

Re: Renaming the files in folder

art297 — Tue, 13 May 2014 20:22:57 GMT

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.

Re: Renaming the files in folder

Ksharp — Wed, 14 May 2014 13:40:37 GMT

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

Re: Renaming the files in folder

RW9 — Wed, 14 May 2014 14:20:55 GMT

Or replace the:

put new;

With:

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

No need for batch file then.

Re: Renaming the files in folder

pp2014 — Wed, 14 May 2014 15:12:24 GMT

Thanks a lot Ksharp. It worked..

Re: Renaming the files in folder

art297 — Thu, 15 May 2014 00:07:07 GMT

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;

Re: Renaming the files in folder

Ksharp — Fri, 16 May 2014 12:30:31 GMT

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):(Column).

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

Re: Renaming the files in folder

art297 — Fri, 16 May 2014 14:09:00 GMT

: 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

Re: Renaming the files in folder

Linlin — Fri, 16 May 2014 14:19:40 GMT

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\;

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

5 data _null_;

7 infile x length=len;

9 input name : $varying40. len;

11 if len=10 then do;

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

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

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

19 end;

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

Re: Renaming the files in folder

art297 — Fri, 16 May 2014 14:35:36 GMT

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

Re: Renaming the files in folder

Linlin — Fri, 16 May 2014 14:48:01 GMT

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

Re: Renaming the files in folder

slchen — Sat, 17 May 2014 01:27:19 GMT

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;