Colorize background of grouped headers with tagset excelXP

[cberetta]

Hi all,

I'm trying to colorize the grouped headers in my tagset.ExcelXP report, I know ho to colorize the headers of the ungrouped columns but do not know how to change the background color of the grouped ones.

This is the sample code I'm using:

data work.db;
infile datalines delimiter=';' dsd flowover;
input col01 col02 col03 col04 col05;
datalines4;
1;2;3;4;5
6;7;8;9;10
11;12;13;14;15
;;;;
run;

ods tagsets.ExcelXP file="c:\temp\example.xls";
proc report data=work.db nowd missing nocenter;
	column
	("group 1" col01 col02 col03 )
	("group 2" col04 col05 )
	;
	define col01 / display style(header)={background=cxFFC000};
	define col02 / display style(header)={background=cxFFC000};
	define col03 / display style(header)={background=cxFFC000};
	define col04 / display style(header)={background=cxC0FF00};
	define col05 / display style(header)={background=cxC0FF00};
run;
ods tagsets.ExcelXP close;

When I run this code I obtain this result:

And below what I'm trying to obtain:

How I had to modify my code to define the background color of the grouped cell "group 1" and "group 2"?

I tried with "inline formatting" but got no result (you can see my modified version of the code below)

ods escapechar = '^';
ods tagsets.ExcelXP file="c:\temp\example.xls"; proc report data=work.db nowd missing nocenter style={protectspecialchars=off}; column ("^{style[background=cxFF0000]}group 1" col01 col02 col03 ) ("group 2" col04 col05 ) ; define col01 / display style(header)={background=cxFFC000}; define col02 / display style(header)={background=cxFFC000}; define col03 / display style(header)={background=cxFFC000}; define col04 / display style(header)={background=cxC0FF00}; define col05 / display style(header)={background=cxC0FF00}; run; ods tagsets.ExcelXP close;

Surely I'm missing something but I cannot "see" what I'm missing 🙂

Someone can help or give me a suggestion?

Thanks in advance

Costantino

[svh · Accepted solution]

Hello,

The answer to this issue involves deriving new variables in the data set (a dummy variable that will not be printed and which will be used as a fake 'Order' variable and two other variables that are the text of your spanning headers). This posting has more details about the usage of these new variables to be able to add background color to your spanning headers. https://communities.sas.com/t5/ODS-and-Base-Reporting/Colouring-spaning-column-headers-in-different-...

You can see, in my code below, that I have added new variables to your data set and I have revised the PROC REPORT code to make it work. I do not know of another way to customize the spanning headers with different colors other than this innovative way provided by @Cynthia_sas 

data work.db;
infile datalines delimiter=';' dsd flowover;
input col01 col02 col03 col04 col05;
datalines4;
1;2;3;4;5
6;7;8;9;10
11;12;13;14;15
;;;;
run;
data work.db; /**Variables are derived to make proc report work**/
   set work.db;
   grp = 1;
   z = "Group 1";
   a = "Group 2";
   run;

ods excel file="c:\temp\example.xlsx";
proc report data=work.db ;
 column grp z, (col01 col02 col03) a, (col04 col05);
 define grp /order noprint; /* This variable is not printed, but is necessary*/
 	define z / across ' '
style(header)={background=pink};
	define a / across ' '
style(header)={background=blue};
 	define col01 / display style(header)={background=cxFFC000};
	define col02 / display style(header)={background=cxFFC000};
	define col03 / display style(header)={background=cxFFC000};
	define col04 / display style(header)={background=cxC0FF00};
	define col05 / display style(header)={background=cxC0FF00};

run;
ods excel close;

[Cynthia_sas]

Hi:
As @svh explained, the use of helper variables or dummy variables gives you the most control, especially if you want the spanning headers to have different background colors. If all the spanning headers can have the same color, then you may not need the helper variables. Here's a paper that illustrates this and other more complex style changes especially when ACROSS items are being used: https://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf .
Cynthia

[cberetta]

Thanks for the great solution!
It's what I was looking for.