Coloring Report Columns using Proc Tabulate

[eduong58]

Hi. I'm trying to create this report view using Proc Tabulate, but I cannot apply color on the first row to the bottom of the table. Can you please advise what's wrong with my code? Thank you so much in advance!

Sample Data

data test;
input name$ Sport$ Status$ cnt;
datalines;
Berlin running win 10
Berlin running lose 10
Berlin running tied 10
Mainz running win 20
Mainz running lose 20
Berlin soccer win 5
Berlin soccer lose 6
Berlin soccer tied 7
Mainz soccer win 8
Mainz soccer tied 9
Berlin swimming win 10
Berlin swimming lose 8
Berlin swimming tied 9
Mainz swimming win 11
Mainz swimming lose 15
Mainz swimming tied 2
;run;

Goal: apply different color on each sport block

what I have now: the color only apply on sport cell. I think I might put the style=<parent> in the wrong place.

here is my code:

PROC FORMAT;
VALUE $COLOR
'running'='lightorange'
'soccer'='lightgreen'
'swimming'='yellow';
RUN;

proc tabulate data=test ;
class name Sport Status ;
classlev Sport / s=[background=$COLOR.];
classlev Status /style=<parent>;
var cnt / style=<parent>;
keyword sum / style=<parent>;
keylabel sum=' ';
table name=' ',Sport = ' '*Status=' '*cnt=' '*
{style=<parent> {foreground=black}}
;run;

[Ksharp]

That is really uneasy. Maybe @Cynthia_sas  could give you a short cut !

And what destination you want to use ?

data test;
input name$ Sport$ Status$ cnt;
datalines;
Berlin running win 10
Berlin running lose 10
Berlin running tied 10
Mainz running win 20
Mainz running lose 20
Berlin soccer win 5
Berlin soccer lose 6
Berlin soccer tied 7
Mainz soccer win 8
Mainz soccer tied 9
Berlin swimming win 10
Berlin swimming lose 8
Berlin swimming tied 9
Mainz swimming win 11
Mainz swimming lose 15
Mainz swimming tied 2
;run;

PROC FORMAT;
VALUE $COLOR
'running'='lightorange'
'soccer'='lightgreen'
'swimming'='yellow';
RUN;


data test;
 set test;
 length new_Status $ 200;
 if Sport='running' then new_Status=cats('(*ESC*)S={background=lightorange}',Status);
 if Sport='soccer' then new_Status=cats('(*ESC*)S={background=lightgreen}',Status);
 if Sport='swimming' then new_Status=cats('(*ESC*)S={background=yellow}',Status);
run;


ods rtf file='c:\temp\temp.rtf' bodytitle ;
options missing='0';
proc report data=test nowd out=temp;
column name cnt,Sport,new_Status;
define name/group;
define Sport/across ' ' style={background=$COLOR.};
define new_Status/across nozero ' ';
define cnt/analysis sum ' ';

compute cnt;
call define('_c5_','style','style={background=lightorange}');
call define('_c6_','style','style={background=lightorange}');
call define('_c7_','style','style={background=lightorange}');

call define('_c11_','style','style={background=lightgreen}');
call define('_c12_','style','style={background=lightgreen}');
call define('_c13_','style','style={background=lightgreen}');

call define('_c26_','style','style={background=yellow}');
call define('_c27_','style','style={background=yellow}');
call define('_c28_','style','style={background=yellow}');
endcomp;
run;
ods rtf close;

[eduong58]

hi @Ksharp . Thank you for your reply. I want to print this report in email. I'm not sure if I can use "proc report" instead of "proc tabulate" since I want to add 2 extra Total columns at the report. Can you please advise if your way create these 2 Total columns? Here is the view of my report:

Code I used: (exactly like the one i posted in question with 2 extra lines at the end)

proc tabulate data=test ;
class name Sport Status ;
classlev Sport / s=[background=$COLOR.];
classlev Status /style=<parent>;
var cnt / style=<parent>;
keyword sum / style=<parent>;
keylabel sum=' ';
table name=' ',Sport = ' '*Status=' '*cnt=' '* {style=<parent> {foreground=black}}
ALL=' ' * Status='Total' * {STYLE={BACKGROUND=lightgrey}} * cnt=' '
ALL='Each Person Total' * {STYLE={BACKGROUND=lightyellow}}* cnt=' '
;run;

[Cynthia_sas · Accepted solution]

Hi:

  If you want the default TABULATE output using <parent> style overrides, it seems to me that you are doing a variation of the Retail/Wholesale example in this Tech Support note:

https://support.sas.com/kb/25/401.html

as shown below:

The Full Code tab in the note has the code and data that created this example.

Cynthia

[Ksharp]

It looks like  the solution posted by @Cynthia_sas  can't get your job done.

Try this one :

data test;
input name$ Sport$ Status$ cnt;
datalines;
Berlin running win 10
Berlin running lose 10
Berlin running tied 10
Mainz running win 20
Mainz running lose 20
Berlin soccer win 5
Berlin soccer lose 6
Berlin soccer tied 7
Mainz soccer win 8
Mainz soccer tied 9
Berlin swimming win 10
Berlin swimming lose 8
Berlin swimming tied 9
Mainz swimming win 11
Mainz swimming lose 15
Mainz swimming tied 2
;run;

PROC FORMAT;
VALUE $COLOR
'running'='lightorange'
'soccer'='lightgreen'
'swimming'='yellow';
RUN;


data test;
 set test;
 cnt1=cnt; cnt2=cnt;cnt3=cnt;cnt4=cnt;
 if Sport='running' then new_Status1=Status; else do;new_Status1='dummy';cnt1=.;end;
 if Sport='soccer'  then new_Status2=Status; else do;new_Status2='dummy';cnt2=.;end;
 if Sport='swimming' then new_Status3=Status;else do;new_Status3='dummy';cnt3=.;end;
run;



options missing='0';
proc report data=test nowd out=temp;
column name cnt1,Sport,new_Status1 cnt2,Sport,new_Status2 cnt3,Sport,new_Status3   cnt,Status  cnt4 ;  
define name/group;
define Sport/across '' style={background=$COLOR.};

define new_Status1/across nozero '' style={background=lightorange};
define new_Status2/across nozero '' style={background=lightgreen};
define new_Status3/across nozero '' style={background=yellow};
define Status/across nozero '' style={background=red};

define cnt1/analysis sum '' style={background=lightorange};
define cnt2/analysis sum '' style={background=lightgreen};
define cnt3/analysis sum '' style={background=yellow};
define cnt/analysis sum 'Total' style={background=red} ;
define cnt4/analysis sum 'Each Person Total' ;

run;

[Cynthia_sas]

Hi:

  Not so fast @Ksharp! The code in the solution I posted DOES work, as shown below:

Even if you blank out the header for the STATUS variable the CLASS statement for STATUS still needs to inherit from the parent cells above it. So the CLASS statement that I added to the original code, works as advertised in the Tech Support note. PROC REPORT is great, but not necessary, in this instance.

Cynthia

[Ksharp]

It is awesome. And glad to see it is working .

data test;
input name$ Sport$ Status$ cnt;
datalines;
Berlin running win 10
Berlin running lose 10
Berlin running tied 10
Mainz running win 20
Mainz running lose 20
Berlin soccer win 5
Berlin soccer lose 6
Berlin soccer tied 7
Mainz soccer win 8
Mainz soccer tied 9
Berlin swimming win 10
Berlin swimming lose 8
Berlin swimming tied 9
Mainz swimming win 11
Mainz swimming lose 15
Mainz swimming tied 2
;run;

PROC FORMAT;
VALUE $COLOR
'running'='lightorange'
'soccer'='lightgreen'
'swimming'='yellow';
RUN;

proc tabulate data=test ;
class name Sport Status / style=<parent>;

classlev Sport / s=[background=$COLOR.];
classlev Status /style=<parent>;
var cnt / style=<parent>;
keyword sum / style=<parent>;
keylabel sum=' ';
table name=' ',Sport = ' '*Status=' '*cnt=' '* {style=<parent> {foreground=black}}
ALL=' ' * Status='Total' * {STYLE={BACKGROUND=lightgrey}} * cnt=' '
ALL='Each Person Total' * {STYLE={BACKGROUND=lightyellow}}* cnt=' '
;run;

[eduong58]

Thank you so much for you two help @Ksharp @Cynthia_sas !!! It works perfectly 🙂 Also, I want to add the Grand Total row at the end of the report, and want to apply the background RED color across the table. I'm not sure if its possible to overwrite the color we assigned for Sport or not.

here is the new piece that I added to the code but does not work:

PROC FORMAT;
VALUE $COLOR
'running'='lightorange'
'soccer'='lightgreen'
'swimming'='yellow';
RUN;

proc tabulate data=test ;
class name Sport Status / style=<parent>;
classlev Sport / s=[background=$COLOR.];
classlev Status /style=<parent>;
var cnt / style=<parent>;
keyword sum / style=<parent>;
keylabel sum=' ';

table name=' ' ALL='Grand Total' * {STYLE={BACKGROUND=lightred}}
,
Sport = ' '*Status=' '*cnt=' '* {style=<parent> {foreground=black}}
ALL=' ' * Status='Total' * {STYLE={BACKGROUND=lightgrey}} * cnt=' '
ALL='Each Person Total' * {STYLE={BACKGROUND=lightyellow}}* cnt=' '

;run;

[Ksharp]

Sorry. I can't help you . Maybe @Cynthia_sas  know the solution ?

Here is PROC REPORT code for your question.

data test;
input name$ Sport$ Status$ cnt;
datalines;
Berlin running win 10
Berlin running lose 10
Berlin running tied 10
Mainz running win 20
Mainz running lose 20
Berlin soccer win 5
Berlin soccer lose 6
Berlin soccer tied 7
Mainz soccer win 8
Mainz soccer tied 9
Berlin swimming win 10
Berlin swimming lose 8
Berlin swimming tied 9
Mainz swimming win 11
Mainz swimming lose 15
Mainz swimming tied 2
;run;

PROC FORMAT;
VALUE $COLOR
'running'='lightorange'
'soccer'='lightgreen'
'swimming'='yellow';
RUN;


data test;
length name $ 80;
 set test;
 cnt1=cnt; cnt2=cnt;cnt3=cnt;cnt4=cnt;
 if Sport='running' then new_Status1=Status; else do;new_Status1='dummy';cnt1=.;end;
 if Sport='soccer'  then new_Status2=Status; else do;new_Status2='dummy';cnt2=.;end;
 if Sport='swimming' then new_Status3=Status;else do;new_Status3='dummy';cnt3=.;end;
run;



options missing='0';
proc report data=test nowd out=temp;
column name cnt1,Sport,new_Status1 cnt2,Sport,new_Status2 cnt3,Sport,new_Status3   cnt,Status  cnt4 ;  
define name/group style=header '';
define Sport/across '' style={background=$COLOR.};

define new_Status1/across nozero '' style={background=lightorange};
define new_Status2/across nozero '' style={background=lightgreen};
define new_Status3/across nozero '' style={background=yellow};
define Status/across nozero '' ;

define cnt1/analysis sum '' style={background=lightorange};
define cnt2/analysis sum '' style={background=lightgreen};
define cnt3/analysis sum '' style={background=yellow};
define cnt/analysis sum 'Total' style(column)={background=lightgrey};
define cnt4/analysis sum 'Each Person Total' style(column)={background=lightyellow} ;

compute cnt4;
if _BREAK_='_RBREAK_' then do;
  name='Grand Total';
  call define(_row_,'style','style=header{background=lightred}');
end;
endcomp;
rbreak after /summarize ;
run;

[Cynthia_sas]

Hi:

  Using my test data and previous code, it was an easy modification, as shown below:

When you have the possibility of a "collision" of style override values, as would happen in the Grand Total row, then you need to add the style_precedence option on the TABLE statement, along with the 2 other style overrides.

Cynthia

[Ksharp]

Cynthia,

When I using your code , I got this . Any idea ?

data test;
input name$ Sport$ Status$ cnt;
datalines;
Berlin running win 10
Berlin running lose 10
Berlin running tied 10
Mainz running win 20
Mainz running lose 20
Berlin soccer win 5
Berlin soccer lose 6
Berlin soccer tied 7
Mainz soccer win 8
Mainz soccer tied 9
Berlin swimming win 10
Berlin swimming lose 8
Berlin swimming tied 9
Mainz swimming win 11
Mainz swimming lose 15
Mainz swimming tied 2
;run;

PROC FORMAT;
VALUE $COLOR
'running'='lightorange'
'soccer'='lightgreen'
'swimming'='yellow';
RUN;

proc tabulate data=test ;
class name Sport ;
class Status / style=<parent>;
classlev Sport / s=[background=$COLOR.];
classlev Status /style=<parent>;
var cnt / style=<parent>;
keyword sum / style=<parent>;
keylabel sum=' ';
keyword all/style={background=lightred};
table name=' ' ALL='Grand Total' * {STYLE={BACKGROUND=lightred}}
,
Sport = ' '*Status=' '*cnt=' '* {style=<parent> {foreground=black}}
ALL=' ' * Status='Total' * {STYLE={BACKGROUND=lightgrey}} * cnt=' '
ALL='Each Person Total' * {STYLE={BACKGROUND=lightyellow}}* cnt=' '
/style_precedence=row;
;
run;

[Cynthia_sas]

Hi:
Your KEYWORD statement for ALL is coloring the last row and the ALL headers in the column dimension too.If you take out the KEYWORD statement, you'll still get the red cells on the grand total line, but the cell for Grand Total won't be red background. So, in that case, if controlling the ALL headers is critical, that may be a reason to move to PROC REPORT>
Cynthia

[Ksharp]

Hi Cynthia,

You mean PROC TABULATE as far as can do this ?

data test;
input name$ Sport$ Status$ cnt;
datalines;
Berlin running win 10
Berlin running lose 10
Berlin running tied 10
Mainz running win 20
Mainz running lose 20
Berlin soccer win 5
Berlin soccer lose 6
Berlin soccer tied 7
Mainz soccer win 8
Mainz soccer tied 9
Berlin swimming win 10
Berlin swimming lose 8
Berlin swimming tied 9
Mainz swimming win 11
Mainz swimming lose 15
Mainz swimming tied 2
;run;

PROC FORMAT;
VALUE $COLOR
'running'='lightorange'
'soccer'='lightgreen'
'swimming'='yellow';

RUN;

proc tabulate data=test ;
class name Sport ;
class Status / style=<parent>;
classlev Sport / s=[background=$COLOR.];
classlev Status /style=<parent>;
var cnt / style=<parent>;
keyword sum / style=<parent>;
keylabel sum=' ';
*keyword all/style={background=lightred};
table name=' ' ALL='Grand Total' * {STYLE={BACKGROUND=lightred}}
,
Sport = ' '*Status=' '*cnt=' '* {style=<parent> {foreground=black}}
ALL=' ' * Status='Total' * {STYLE={BACKGROUND=lightgrey}} * cnt=' '
ALL='Each Person Total' * {STYLE={BACKGROUND=lightyellow}}* cnt=' '
/style_precedence=row;
;
run;

[Cynthia_sas]

Hi:
Yes, as you can see the cell with the Word "Grand Total" is not red background, but the data cells on the summary line are red. That also allows the column headers for Total and Each Person Total to be the default color for the ALL, too.

Cynthia

[eduong58]

@Ksharp , I got the same output with you since  I don't want to apply RED to other ALL columns.  So I added the separate style for Grand Total itself and it works. 

proc tabulate data=test ;
class name Sport ;
class Status / style=<parent>;
classlev Sport / s=[background=$COLOR.];
classlev Status /style=<parent>;
var cnt / style=<parent>;
keyword sum / style=<parent>;
keylabel sum=' ';
*keyword all/style={background=lightred};
table name=' ' ALL={Label='Grand Total' style={BACKGROUND=lightred}} * {STYLE={BACKGROUND=lightred}}
,
Sport = ' '*Status=' '*cnt=' '* {style=<parent> {foreground=black}}
ALL=' ' * Status='Total' * {STYLE={BACKGROUND=lightgrey}} * cnt=' '
ALL='Each Person Total' * {STYLE={BACKGROUND=lightyellow}}* cnt=' '
/style_precedence=row;
;
run;