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:
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.