BookmarkSubscribeRSS Feed
pp2014
Fluorite | Level 6

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;

11 REPLIES 11
art297
Opal | Level 21

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.

Ksharp
Super User

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Or replace the:

put new;

With:

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

No need for batch file then.

pp2014
Fluorite | Level 6

Thanks a lot Ksharp.  It worked..

art297
Opal | Level 21

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;

Ksharp
Super User

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

art297
Opal | Level 21

: 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

Linlin
Lapis Lazuli | Level 10

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

art297
Opal | Level 21

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

Linlin
Lapis Lazuli | Level 10

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

slchen
Lapis Lazuli | Level 10

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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 3261 views
  • 0 likes
  • 6 in conversation