Colorize background of grouped headers with tagsets excelXP

user_5758202 0
Viewed 48

I'm trying to colorize the grouped headers in my tagsets.ExcelXP report, I know how 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: Output of above code

And here what I'm trying to obtain: Output that 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

enterprise-guide sas
1 Answers

So, this is somewhat complicated, unfortunately.

If you have at least one column that's not under the groupings, you can do it with a text variable - but this only works if there's at least one other column, for reasons that don't entirely make sense to me.

For example:

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

ods tagsets.ExcelXP file="c:\temp\example.xls";
proc report data=work.db nowd missing nocenter;
    column id
    group1,(col01 col02 col03 ) 
    group2,(col04 col05 )
    ;
    define id/display;
    define group1/' ' across noprint style(header)={background=orange};
    define group2/' ' across noprint style(header)={background=lightblue};
    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;

The ' ' makes the actual group variable label not show, so instead it shows the text inside the variable, and that works as you can easily style that.

If you don't, then the only option I see other than DDE or SAS Office Add-in (DDE is not a great option as it's deprecated and not well supported, SAS Office add-in is a great option but it's a separate product licensing wise) is maybe doing something complicated using a CSS style... you could identify the class the groups belong to and do a first-child second-child selector. But that's really complicated and basically manual - if anything changes you have to remember to change the CSS. Something like in Cascading Style Sheets: Breaking Out of the Box of ODS Styles by Kevin Smith.

user_16230070
Related