Appending Macro Variable Name to End of Export File

[alexgouv]

Hi,

I am trying to append a macro to the end of an excel file that I am exporting, so the new file name will be filename_macro.xlsx. 

The macro variable is a date and right now it is outputting as the SAS date(just a number) with a space in front of the number. For example it looks like filename_ 21074.xlsx.

My final goal is to be able to compare the file from this week to the file from last week. I don't know if this space needs to be deleted but I was planning on doing a compare on the current file and the file named filename_(macro-7). If there is an easier way to do this please let me know!

[ballardw]

You kind of need to show the existing code you are using/ trying. The are many ways this might be accomplished and knowing what you start with will make responses much more useful.

Post code into a code box opened using the forum's {I} or "running man" icons to preserve layout.

If instead of 21074 you want something related to 12SEP2017 then somewhere you need to use the proper display format for the value, which you have not provided.

[Tom · Accepted solution]

Sounds like you want use the value of macro variable to construct a filename?

So if your macro variable has the value 21074 and you want to interpret that as a date and generate a human readable string that you could include as part of the filename you need to use a FORMAT.  I would recommend either YYMMDDD10. (2017-09-12) or YYMMDDN8. (20170912) so that your resulting filenames will sort in chronological order.

Perhaps you can apply the format earlier so that the macro variable already looks like the string you want. Or else use the %SYSFUNC() to call the PUTN() function to apply the format to the value.

%let date=21074;
%let filename=filename_%sysfunc(putn(&date,yymmddn8.)).xlsx;
proc export file="&filename" ....