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)
The University of Alabama
&R&fdate, Page &P of &N
&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!
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:
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 
, 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
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:
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.