ods excel (header) and macro language

[xxformat_com]

Hi,

How to right align an ISO date in the page header (Excel destination) using print_header= option?

Note that it works fine with the title statement

ods excel file="&xxtest./reporting/ods_excel_test.xlsx";


ods excel options(sheet_name='&R&D'
                  print_header='&R&D');

proc print data=sashelp.class noobs;
run;

ods excel options(sheet_name='%sysfunc'
                  print_header="%sysfunc(today(),e8601da.)");

proc print data=sashelp.class noobs;
run;

*issue;
ods excel options(sheet_name='&R %sysfunc'
                  print_header="&R %sysfunc(today(),e8601da.)");

proc print data=sashelp.class noobs;
run;

*works properly;
ods excel options(sheet_name='Title');

title '&R' "%sysfunc(today(),e8601da.)";

proc print data=sashelp.class noobs;
run;

ods excel close;

1. Excel/VBA command: &D but I don't know how to format it.
2. %sysfunc(today(),e8601da.) generated an ISO date but is centered here.
3. &R %sysfunc(today(),e8601da.) works but logically enough, the SAS systems tries to solve &R and a message is displayed when opening the Excel file. %nrstr(&R) does not solve the issue here.
4. OK with the title statement as we can concatenate strings.

Thanks in advance for any tips.

[Kurt_Bremser]

When I use %NRSTR(&R) there are (of course) no messages in the log, and when I open the file in LibreOffice and use the print preview, the date is right-aligned.

[xxformat_com]

Thanks @Kurt_Bremser 

When I run the code I get the following error message when tempting to open the Excel file. Do you see any difference with your code?

ods excel file="&xxtest./reporting/ods_excel_test.xlsx";

ods excel options(print_header="%nrstr(&R) %sysfunc(today(),e8601da.)");

proc print data=sashelp.cars (obs=50) noobs;
run;

ods excel close;

[xxformat_com]

ps. I'm SAS Studio 3.81 using 9.04.01M5. I've never been able to get the last maintenance even by contacting the SAS support.

[xxformat_com · Accepted solution]

@Kurt_Bremser Your answer helped me realise that the issue could me my SAS installation. I've justed tested it on SAS On-Demand. It works there. So the issue is with my installation somehow. Cheers

[SASKiwi]

SAS On-Demand uses SAS 9.4 M6 I believe, so you'll need to upgrade to at least that in your local SAS installation.

[Kurt_Bremser]

ODS EXCEL is fairly new (and Excel is always a moving target), so it's no miracle that we repeatedly find (fixed) bugs or inconsistencies. Keeping SAS up-to-date is always a good idea.