Rename External File without Overwriting It

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Rename External File without Overwriting It

Hi Guys,

Using SAS, is it possible to batch rename a file to create multiple files with the same content and different filenames in a windows directory...without overwriting the original filename?

Example:

Directory : C:\Reports\

Original File: MASTER_Report-MONYYYY.xlsm

New Files to be made from the original: Group1_Report-MONYYYY.xlsm |  Group2_Report-MONYYYY.xlsm | Group3_Report-MONYYYY.xlsm

Essentially, I need to rename "MASTER" of the original file to each group's name at the beginning of a macro I've built that overwrites the file with that group's data. Also, the filenames need to have the date included in the MONYYYY. format. This program will be running each month, and there is no telling how many groups there will be month to month. As of now, I have about 100 files named Group1, Group2, Group3, etc... that are being overwritten each month, but I have to manually "Save As" whenever there is another group added.  I'm wondering if there is a SAS command that goes to a directory...copies MASTER_Report-MONYYYY.xlsm and renames it to Group1_Report-MONYYYY.xlsm without replacing MASTER_Report-MONYYYY.xlsm.

Here is the code I have thus far:

data _Null_;

  call symputx("od",put(intnx('month', today(),  -1, 'end'),monyy.));

run;

%Let od=&od.;

%put &od.;

filename zen pipe 'dir "C:\Reports\MASTER_Report-MONYYYY.xlsm" /b /s';

%macro Groups;

%do f=1 %to 3;

data zen;

infile zen pad;

input old $250.;

new=tranwrd(old,"MASTER_Report-MONYYYY","Group&f._Report-&od.");

run;

data _null_;

set zen;

rc=rename(old, new, "file");

put rc;

run;

%end;

%mend Groups;

%Groups;

Thanks!

p.s. I have my report template saved in a nicely laid-out and functional .XLSM file. It depends on these Excel macros, and I cannot change it without the functionality of the dashboards and user-defined graphs being messed up. I hope this doesn't make what I'm trying to accomplish impossible.


Accepted Solutions
Solution
‎04-28-2015 10:37 PM
Super User
Posts: 19,038

Re: Rename External File without Overwriting It

Since you're on windows X copy, assuming you have x commands enabled:

X copy "C:\Reports\MASTER_Report-MONYYYY.xlsm" "C:\Reports\Group1_Report-MONYYYY.xlsm";

View solution in original post


All Replies
Super User
Posts: 11,105

Re: Rename External File without Overwriting It

If it has to be done in SAS the best bet would be to use the SAS X or Call System commands to issue the appropriate operating system command to rename a file (or directory) .

Solution
‎04-28-2015 10:37 PM
Super User
Posts: 19,038

Re: Rename External File without Overwriting It

Since you're on windows X copy, assuming you have x commands enabled:

X copy "C:\Reports\MASTER_Report-MONYYYY.xlsm" "C:\Reports\Group1_Report-MONYYYY.xlsm";

Super User
Posts: 9,856

Re: Rename External File without Overwriting It

OS command is the best one . But you can also try RENAME()  function .

Super User
Super User
Posts: 7,686

Re: Rename External File without Overwriting It

Hi,

Sorry, struggling to see this.  You talk about XLSM (which is macro enabled Open Office document), and macros.  So my question would be why are you doing this in SAS?  If you have it setup in VBA, just write the VBA code to save as in separate files.  What your problem seems to be is that your trying to shoe horn two very different software packages into one process.  Make a choice, do stuff in SAS and generate reports, or do stuff in Excel and use VBA.  Personally I would avoid Excel for any kind of real data usage as it has no structure, or control, plus its not a properly validated suite.  The question arises why you cannot produce the outputs required in SAS, where is your data for instance?

Occasional Contributor
Posts: 5

Re: Rename External File without Overwriting It

SAS reports are too simplistic and finalized for what I am trying to accomplish. Don't get me wrong, I highly prefer using SAS to Excel, but there is no way I know of creating dashboards, graphs, and pivot tables in SAS that users can play around with; that is, tools that allow end users to go interchangeably between metrics and groups then see their behavior on graphs in real time. I can't expect every group or customer I have to send reports to to have SAS licences, thus Excel and/or CSV files are the best way for me to send my data to those customers....after I have cleaned and analyzed the data in SAS.

Super User
Super User
Posts: 7,686

Re: Rename External File without Overwriting It

SAS Web Report studio, SAS Visual Analytics, JReview, etc. There are fair few tools out there which allow users to connect to data and create report/graphs etc.  I would suggest if someone wants to play with data and states they want to use Excel, then I would be very worried about it.   Its really good fun when several years down the line an authority comes back with questions on a nice Excel grid they were provided...  It happens, have a quick scan over: Researchers Finally Replicated Reinhart-Rogoff, and There Are Serious Problems. | Next New Deal

As as SAS programmer I would expect you have strict guidelines, SOPs, and such like to ensure validation is carried out to avoid errors.

Occasional Contributor
Posts: 5

Re: Rename External File without Overwriting It

Interesting article,...but I must say "When in Rome...", thus I will continue to output my reports to what the Romans use.

Super User
Super User
Posts: 6,842

Re: Rename External File without Overwriting It

I do not understand what you are trying to do.

You cannot RENAME one file to three different file names.  You can COPY the file.

data zen ;

  length old new $256 ;

  infile 'dir "C:\Reports\MASTER_Report-MONYYYY.xlsm" /b /s' pipe truncover ;

  input old $256. ;

  do group=1 to 3 ;

     new=tranwrd(old

                ,'MASTER_Report-MONYYYY'

                ,cats('Group',group,'Report-',put(intnx('month', today(), -1, 'end'),monyy.)))

                );

     rc=fcopy(old,new);

  end;

run;

Occasional Contributor
Posts: 5

Re: Rename External File without Overwriting It

Thanks! I've never used the X statement. I'm going to read up on all that it can do. Here's my final code that takes into account the macro date variable:

data _Null_;

  call symputx("od",put(intnx('month', today(),  -1, 'end'),monyy.));

run;

%Let od=&od.;

%put &od.;

%macro Groups;

%do f=1 %to 3;

data _null_;

file="' copy "||'"'||"C:\Reports\MASTER_Report-MONYYYY.xlsm"||'"'||' "'||"C:\Reports\Group&f._Report-&od..xlsm"||'"'||"'";

call symputx('file',file);

run;

X &file.;

%end;

%mend Groups;

%Groups;

Super User
Super User
Posts: 6,842

Re: Rename External File without Overwriting It

Note that if you are using an older version of SAS that does not have the FCOPY() function you can use the FILEVAR option on the INFILE statement with PIPE engine execute the COPY commands via the operating system.

%let od=%sysfunc(intnx(month,%sysfunc(today()),-1,end),monyy7);

%put &od;

data zen ;

  length old new $256 ;

  infile 'dir "C:\Reports\MASTER_Report-MONYYYY.xlsm" /b /s' pipe truncover ;

  input old $256. ;

  do group=1 to 3 ;

     new=tranwrd(old

                ,'MASTER_Report-MONYYYY'

                ,cats('Group',group,"_Report-&od")

                );

     cmd=catx(' ','copy',old,new);

         infile cmd pipe filevar=cmd end=eof;

     while (not eof);

            input ;

           put _infile_;

     end;

   output;

  end;

run;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 573 views
  • 7 likes
  • 6 in conversation