Set a background color for spanned header in PROC REPORT: ODS Excel destination
When you write PROC REPORT output to ODS destinations, you can add colors and other style attributes to headers, including column headers and spanning headers. For individual column headers, all destinations honor the background and foreground colors, as shown below. However, for spanning headers, the Excel destination does not apply the background color to the spanning header as you would expect. The sample code below illustrates this issue, and the resulting Excel and RTF output are provided.
ods _all_ close;
ods escapechar='^';
ods excel file='test.xlsx';
ods rtf file='test.rtf';
proc report data=sashelp.class
style(column)=[cellwidth=1in];
where age le 13;
column sex age
('^S={background=green foreground=orange} Statistics' height weight);
define age / group 'how old are you?'
style(header)=[background=yellow foreground=red];
define sex / group 'gender';
define height / mean 'average height' format=8.2;
define weight / mean 'average weight' format=8.2;
run;
ods _all_ close;
ods listing;
RTF output (looks as expected):
Excel output (background color missing!):
Don’t worry! You can get a spanning header with a background color in the Excel destination by adding a character variable to the data set that contains the text of the desired spanning header. You can then use this variable as an ACROSS variable and apply the background color to that variable.
data class;
set sashelp.class;
hdr='Statistics';
run;
ods _all_ close;
ods escapechar='^';
ods excel file='test1.xlsx';
proc report data=class
style(column)=[cellwidth=1in];
where age le 13;
column sex age hdr,(height weight);
define hdr / across ' ' style=[background=green foreground=orange];
define age / group 'how old are you?'
style(header)=[background=yellow foreground=red];
define sex / group 'gender';
define height / mean 'average height' format=8.2;
define weight / mean 'average weight' format=8.2;
run;
ods _all_ close;
ods listing;
Excel output (now correct!):
That’s all there is to it! Now the spanning header contains the desired background color in your Excel table.
Learn more about PROC REPORT from Jane Eslinger's book: The SAS Programmer's PROC REPORT Handbook.
