SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Put date on the end of xls file name

Reply
Contributor
Posts: 58

Put date on the end of xls file name

In the following code, I would like to save the Excel file with the current date.

PROC EXPORT Data= Work.Cl_adhoc_rpt
Outfile = "N:\Admin\Linda\SAS\Adhoc_Rept_"&format$(Date,"yyyy-mm-dd")&".xls"
DBMS=EXCEL2000 REPLACE;
RUN;

The outfile = is not correct. Can someone tell me the proper syntax to do this. I am not sure it can be done but if there is some code to do this, I would like to know it.

Thanks
SAS Super FREQ
Posts: 8,643

Re: Put date on the end of xls file name

Hi:
This is really a macro usage question. First you want to CREATE a macro variable. %SYSFUNC will allow you to invoke a DATA step function -- in this case, the TODAY function which will return today's date. One of the unique things about %SYSFUNC is the ability to format the date that's returned from %SYSFUNC -- so &NOW1, &NOW2 and &NOW3 were each created with different formats. If you run the first bit of code and then look in the log, you'll see what the values of the 3 macro variables are:
[pre]
%let now1 = %sysfunc(today(),date9.);
%let now2 = %sysfunc(today(),mmddyy10.);
%let now3 = %sysfunc(today(),yymmddd10.);

%put what is 1 &now1;
%put what is 2 &now2;
%put what is 3 &now3;
[/pre]

What's in the SAS Log:
[pre]
5 %put what is 1 &now1;
what is 1 07FEB2008
6 %put what is 2 &now2;
what is 2 02/07/2008
7 %put what is 3 &now3;
what is 3 2008-02-07

[/pre]

Immediately under each %PUT statement is the result of the %PUT. You can see how &NOW1, &NOW2 and &NOW3 were all resolved. Now, you USE the macro variable...let's pick &NOW3, since it seems to be formatted the way you want (with dashes between the digits). Note that &NOW3 was used to build the &MYFILE macro variable and &MYFILE is used in a TITLE statement. I like to build my big strings out of little macro variables or strings so I can see how the little pieces are being built before I assemble them together.
[pre]
%let myfile = c:\temp\file&now3..xls;

proc print data=sashelp.class(obs=1);
title "The file name is &myfile";
run;
[/pre]

You would change this to be in your OUTFILE= option with a small change
[pre]
%let myfile = N:\Admin\Linda\SAS\Adhoc_Rept_&now3..xls;
Outfile = "&myfile"

OR you could just use &NOW3 in the OUTFILE=

Outfile="N:\Admin\Linda\SAS\Adhoc_Rept_&now3..xls";
[/pre]

Note that macro variables only resolve within double quotes, so any references to &myfile or &now3 in a text string (like a title or an OUTFILE option) need to be in double quotes. Also note the 2 dots &now3..xls in the file reference. Since the filename needs a dot, that explains one dot...however, you have to delimit the end of the macro variable and a single dot will be that delimiter. So you need 2 dots to build the file name. If you did not want to use today's date, or if your date value was coming from the data file itself or if you wanted to use a specific date as a value, then that would require slightly different macro processing and Tech Support could help you with any of those scenarios.

cynthia
Post a Question
Discussion Stats
  • 1 reply
  • 2172 views
  • 0 likes
  • 2 in conversation