Help using Base SAS procedures

Proc Export to Excel File Name Issue

Accepted Solution Solved
Reply
Contributor
Posts: 24
Accepted Solution

Proc Export to Excel File Name Issue

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.


Accepted Solutions
Solution
‎07-14-2014 12:56 PM
Super User
Posts: 19,770

Re: Proc Export to Excel File Name Issue

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;

View solution in original post


All Replies
Super User
Posts: 19,770

Re: Proc Export to Excel File Name Issue

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

Frequent Contributor
Posts: 117

Re: Proc Export to Excel File Name Issue

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;

Super User
Posts: 11,343

Re: Proc Export to Excel File Name Issue

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.

Solution
‎07-14-2014 12:56 PM
Super User
Posts: 19,770

Re: Proc Export to Excel File Name Issue

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;

Contributor
Posts: 24

Re: Proc Export to Excel File Name Issue

Thank you !  This worked perfectly.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 482 views
  • 6 likes
  • 4 in conversation