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;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.