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;
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.
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
Or replace the:
put new;
With:
call execute("x '"||strip(new)||"';");
No need for batch file then.
Thanks a lot Ksharp. It worked..
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;
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
: 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
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
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.