topic Re: Proc Export to Excel File Name Issue in SAS Procedures

Proc Export to Excel File Name Issue

suncawy — Mon, 14 Jul 2014 15:33:37 GMT

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.

Re: Proc Export to Excel File Name Issue

Reeza — Mon, 14 Jul 2014 16:09:20 GMT

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

Re: Proc Export to Excel File Name Issue

Vish33 — Mon, 14 Jul 2014 16:30:19 GMT

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;

Re: Proc Export to Excel File Name Issue

ballardw — Mon, 14 Jul 2014 16:48:54 GMT

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.

Re: Proc Export to Excel File Name Issue

Reeza — Mon, 14 Jul 2014 16:56:41 GMT

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;

Re: Proc Export to Excel File Name Issue

suncawy — Mon, 14 Jul 2014 17:02:25 GMT

Thank you ! This worked perfectly.