topic Re: How do I get multiple footnote lines in ODS EXCEL using options(print_footer=)? in ODS and Base Reporting

How do I get multiple footnote lines in ODS EXCEL using options(print_footer=)?

ChristineKraft — Thu, 15 Feb 2018 16:36:13 GMT

I just upgraded to SAS 9.4 TS1M5 from 9.4 TS1M1 and am excited to use ODS EXCEL. But when I use the print_footer option with the same macro variable (see below) I had been using with ODS TAGSETS.EXCELXP, it ignores the newline character. Has anyone else had this problem and know how to fix it?

Macro I have been using:

%let footer_text =&LInstitutional Research and Assessment (&name)&#13The University of Alabama
 &R&fdate, Page &P of &N&#13&Z&F;

Footer in Excel that I used to get using above footer and ODS TAGSETS.EXCELXP:

Footer in Excel that I now get using the above macro variable and ODS EXCEL:

Using this code:

ods excel 
	file="&fname"
	style = ad_hoc
	options(embedded_titles='yes' 
			embedded_footnotes='yes'
			print_footer="&footer_text"
			sheet_name="UA"
			orientation = 'portrait'
			absolute_column_width="12"
			fittopage = "yes"
			center_horizontal = 'yes'
			title_footnote_width = '7' 
	);

Any help is appreciated! Thanks!

Re: How do I get multiple footnote lines in ODS EXCEL using options(print_footer=)?

Cynthia_sas — Fri, 16 Feb 2018 14:38:59 GMT

Hi:
I do observe the difference you describe, I did not have the "ad_hoc" style, so used the HTMLBLUE style. In your code, you used &#13, I have seen an alternative to that is 
 however, that produced the same results. You might want to open a track with Tech Support about this different behavior of the Alt+Enter for a new line with ODS EXCEL.

cynthia

Re: How do I get multiple footnote lines in ODS EXCEL using options(print_footer=)?

ChristineKraft — Fri, 16 Feb 2018 15:04:20 GMT

Thanks! I will do that!

Re: How do I get multiple footnote lines in ODS EXCEL using options(print_footer=)?

ChristineKraft — Tue, 20 Feb 2018 15:50:54 GMT

After opening a track with SAS Technical Support, Chevell Parker came up with this solution, which works like a charm! Thank you, Chevell!

TITLE;
FOOTNOTE;

ODS excel file="test.xlsx" 
options(sheet_name='TEST_DATA01_05OCT2017D' orientation='portrait' 
print_footer="&amp;LInstitutional Research and Assessment (&name) &#10;The University of Alabama &amp;R &fdate, Page &amp;P of &amp;N &#10; &amp;Z &amp;F");

PROC REPORT DATA=sashelp.class;

define name / style(column)=[just=c];

RUN;

ods excel close;

Or, if you want the footnote in a macro variable as we do in our office, your code could look like this:

%let footer_text =%str(&amp;LInstitutional Research and Assessment (&name)&#10;The University of Alabama &amp;R &fdate, Page &amp;P of &amp;N &#10; &amp;Z &amp;F);

TITLE;
FOOTNOTE;

ODS excel file="test.xlsx" 
options(sheet_name='TEST_DATA01_05OCT2017D' orientation='portrait' 
print_footer="&footer_text");

PROC REPORT DATA=sashelp.class;

define name / style(column)=[just=c];

RUN;

ods excel close;

And the output looks like this in both cases: