Renaming an Excel file using Macros

Reply
New Contributor
Posts: 2

Renaming an Excel file using Macros

Hi,

 

I am trying to download a dataset in SAS 9.3 (in Windows) as an Excel file from a database and save it to my desktop. I do this weekly into a single folder and the code seems to be fine; however, now I need to start saving it in individual weekly folder on desktop (folder for week 1, week 2 and so on..) as well as attach a time and date to the file name. I do this every week and so want to built a macro which would rename the file as mentioned above and create a new folder every week without I having to manually create the folder and rename several Excel files everytime. Any help will be appreciated. Thank you.

Super User
Posts: 17,868

Re: Renaming an Excel file using Macros

DCREATE() function will create a directory/folder

 

DLCREATEDIR option on the libname statement will create a libname reference if it doesn't exist.

 

FCOPY() will move a file (9.4+)

 

If you're using SAS 9.3 on a desktop you can use X commands with COPY to rename the file. 

 

Here's a blog post that illustrates a few way to copy a file, and he links to some other posts which highlight ways to get the file from a website. 

http://blogs.sas.com/content/sasdummy/2013/09/17/copy-file-macro/

 

TODAY() functionwill show todays date to create your folder name. Note that SYSFUNC allows formatting of the date, the second parameter is the date. 

 

%let date_file = %sysfunc(today(), yymmddn8.);
%put &date_file;

I'm not sure why a macro is needed, a data step will probably do all of this.

New Contributor
Posts: 2

Re: Renaming an Excel file using Macros

That is very helpful. Thank you.

Ask a Question
Discussion stats
  • 2 replies
  • 145 views
  • 0 likes
  • 2 in conversation