SAS Macro Variable and file extensions

Question

I have the following code at the end of my code to export the SAS data to an Excel File. The RepDate is set up as follows at the start of the code:

%let RepDate = &SYSDATE9; 

The date works perfectly, however when I use the following code to export the data:

PROC EXPORT DATA=MYData         
OUTFILE="C:\Documents and settings\Documents\myFile &RepDate.XLS"     DBMS=EXCEL2000 REPLACE;
RUN;

The date is entered into the file name okay, but XLS is added to the file name. The dot for the file extension is missing and the file name becomes:

myFile02APR2012XLS

This is not what I expected, I expected the file name to be as follows: myFile02Apr2012 with the .XLS as the file extension. Is there a way to fix this?

Answer 1

You need

OUTFILE="C:\Documents and settings\Documents\myFile &RepDate..XLS"

You need to use an extra . after the &RepDate macro variable reference. The first . terminates the macro variable reference, and the second . is then part of the string.

To understand why the . is necessary, you might want to build a filename

02Apr2012MyFile.XLS

In this case you would want to try and write:

&RepDateMyFile.XLS

but the sas interpreter wouldn't know where the macro variable names ends and the string text continues, so a dot is required in order to end the macro variable reference:

&RepDate.MyFile.XLS

In your case you want to then put a . immediately after the resolved macro variable, hence the need for two dots.