Build reports by using ODS to create HTML, PDF, RTF, Excel, text reports and more!

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

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

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_desired.jpg

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

 

footer.jpg

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!


Accepted Solutions
Solution
‎02-20-2018 10:32 AM
Occasional Contributor
Posts: 5

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

[ Edited ]
Posted in reply to ChristineKraft

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="&LInstitutional Research and Assessment (&name) 
The University of Alabama &R &fdate, Page &P of &N 
 &Z &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(&LInstitutional Research and Assessment (&name)
The University of Alabama &R &fdate, Page &P of &N 
 &Z &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:

 solutiom.jpg

 

View solution in original post


All Replies
SAS Super FREQ
Posts: 9,434

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

Posted in reply to ChristineKraft

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

Occasional Contributor
Posts: 5

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

Posted in reply to Cynthia_sas
Thanks! I will do that!
Solution
‎02-20-2018 10:32 AM
Occasional Contributor
Posts: 5

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

[ Edited ]
Posted in reply to ChristineKraft

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="&LInstitutional Research and Assessment (&name) 
The University of Alabama &R &fdate, Page &P of &N 
 &Z &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(&LInstitutional Research and Assessment (&name)
The University of Alabama &R &fdate, Page &P of &N 
 &Z &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:

 solutiom.jpg

 

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 216 views
  • 0 likes
  • 2 in conversation