Proc Export to Excel File Name Issue

[suncawy]

Hello,

   This should be fairly simple, but can't find a solution.   When I export to an Excel file,  I want the file name to appear as "ABA_RATE_REVIEW (7.10.2014).xls",  but for some odd

reason the file name appears as "ABA_RATE_REVIEW (7.10.2014    ).xls",  that extra space (trailing blank in the date) keeps appearing before the ending parenthesis.   I've tried COMPRESS, TRIM, but none those seem to work in the code.

   Here is the current code:

Data _null_;

    datefmt = DATE()

    TITLEDT = Put (MONTH(datefmt), Z2.) || Put (DAY(datefmt), Z2.) || SUBSTR(LEFT(TRIM(RIGHT(YEAR(datefmt)))),1,4);

    Call SYMPUT ('WEEK_DT', TITLEDT);

Run;

Proc Export Data = ABA

                  DBMS = Excel Replace

                  Outfile = "C:\Documents\ABA_RATE_REVIEW (&WEEK_DT).xls";

                  Sheet = ABA;

Run;

  Would anybody happen to have a solution ? 

Thank you in advance.

[Reeza]

Try call symputx instead of call symput when creating your macro variable.

[Vish33]

Hi,

Use trim in Call symput as follows.

Data _null_;

    datefmt = DATE();

    TITLEDT = Put (MONTH(datefmt), Z2.) || Put (DAY(datefmt), Z2.) || SUBSTR(LEFT(TRIM(RIGHT(YEAR(datefmt)))),1,4);

    Call SYMPUT ('WEEK_DT', trim(TITLEDT));

Run;

%put &WEEK_DT;

[ballardw]

And you may want to investigate using on of the CATT CATS CATX functions instead of concatenating multiple variables with || as those functions can strip leading and/or trailing spaces from each component without additional function calls to TRIM, LEFT or STRIP.

[Reeza · Accepted solution]

You can also skip the data step entirely and use the format mmddyyp10. to get your  macro variable.

%let week_dt=%sysfunc(date(), mmddyyp10.);

%put &week_dt;

title "this is the correct title (&week_dt)";

proc means data=sashelp.class;

run;

[suncawy]

Thank you !  This worked perfectly.