Set a background color for spanned header in PROC REPORT: ODS Excel destination

[#1]

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. 

[tgleghorn]

I am trying to create  separate format for each of my line statements.  this is not working   

line text1 $char100. / style = [&FlagStyle just=left];

[Kathryn_SAS]

In ODS, the LINE statements in a COMPUTE block of PROC REPORT are seen
as one long string. This is also true when there are multiple LINE
statements in the COMPUTE block.

Style attributes, such as PRETEXT=, will apply only once in a LINE
statement cell.

You cannot apply unique styles to each LINE statement.

If you have additional questions, it would be better to submit them in a separate Communities post or open a case with SAS Technical Support.