topic ods excel (header) and macro language in ODS and Base Reporting

ods excel (header) and macro language

xxformat_com — Wed, 16 Feb 2022 13:00:13 GMT

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;

Excel/VBA command: &D but I don't know how to format it.
%sysfunc(today(),e8601da.) generated an ISO date but is centered here.
&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.
OK with the title statement as we can concatenate strings.

Thanks in advance for any tips.

Re: ods excel (header) and macro language

Kurt_Bremser — Wed, 16 Feb 2022 13:20:31 GMT

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.

Re: ods excel (header) and macro language

xxformat_com — Wed, 16 Feb 2022 22:45:22 GMT

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;

Re: ods excel (header) and macro language

xxformat_com — Wed, 16 Feb 2022 22:48:31 GMT

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.

Re: ods excel (header) and macro language

xxformat_com — Wed, 16 Feb 2022 22:53:26 GMT

@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

Re: ods excel (header) and macro language

SASKiwi — Wed, 16 Feb 2022 22:59:12 GMT

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.

Re: ods excel (header) and macro language

Kurt_Bremser — Thu, 17 Feb 2022 08:47:33 GMT

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.