tabulate: overlapping styles at line and row crossing

[ChrisNZ]

Hi,

How can I get the height total in green bold?

I think I used to know that, but no longer...

proc tabulate data=SASHELP.CLASS;
  class AGE ;
  var WEIGHT  HEIGHT  ;
  table AGE all*[s=[color=green]], WEIGHT*sum HEIGHT*sum*[s=[font_weight=bold]];
run ;

[yabwon]

proc tabulate data=SASHELP.CLASS;
  class AGE ;
  var WEIGHT  HEIGHT  ;
  table AGE*[s={font_weight=light}] 
        all*[s={color=green font_weight=bold}]
        , 
        WEIGHT*sum HEIGHT*sum*[s={font_weight=bold}];
run ;

[ChrisNZ]

This does not work on 9.4M2. Does it work for you?

[yabwon]

[EDIT]

Yes, but I'm on 9.4M6.

Sorry. No, it doesn't, wrong variable. 😛

Bart

[yabwon]

One more approach, "scratching left ear with right hand" but seems to do the job:

proc sql noprint;
select 
 sum(WEIGHT) as sum_WEIGHT
,sum(HEIGHT) as sum_HEIGHT
into 
  :sum_WEIGHT
 ,:sum_HEIGHT
from
sashelp.class
;

proc format;
value sum_WEIGHT
&sum_WEIGHT. = green
other = black
;

value sum_HEIGHT
&sum_HEIGHT. = green
other = black
;

value sum_WEIGHT_f
&sum_WEIGHT. = bold
other = light
;

value sum_HEIGHT_f
&sum_HEIGHT. = bold
other = light
;
run;

proc tabulate data=SASHELP.CLASS;
  class AGE;
  var WEIGHT  HEIGHT  ;
  table AGE 
        all
        , 
        WEIGHT*sum*[s={color=sum_WEIGHT. font_weight=sum_WEIGHT_f.}]
        HEIGHT*sum*[s={color=sum_HEIGHT. font_weight=sum_HEIGHT_f.}]
        ;
run ;

Bart

[Ksharp]

Chris,

Do you have to use proc tabulate ? Maybe @ballardw  have an idea.

proc report data=sashelp.class nowd ;
column age _age  weight height;
define age/group noprint;
define _age/computed 'Age' style=header;
define weight/analysis sum;
define height/analysis sum style={font_weight=bold};
rbreak after /summarize ;
compute _age/character length=20;
  _age=ifc(missing(age),'All',put(age,best. -l));
  if _break_='_RBREAK_' then call define(_col_,'style','style=header');
endcomp;
compute height;
  if _break_='_RBREAK_' then call define('weight.sum','style','style={color=green}');
  if _break_='_RBREAK_' then call define('height.sum','style','style={color=green}');
endcomp;
run;

[Ksharp]

Chris,

Or you could try Traffic light .

proc sql noprint;
select int(sum(height)) into : sum from sashelp.class;
quit;
proc format ;
value fmt
&sum.<-high='green'
other='black'
;
run;


proc tabulate data=SASHELP.CLASS;
  class AGE ;
  var WEIGHT  HEIGHT  ;
  table AGE all*[s=[color=green]], WEIGHT*sum HEIGHT*sum*[s=[font_weight=bold color=fmt.]];
run ;

[ChrisNZ]

Thank you for your response guys. I'll need to adapt one of the responses to my (more complex) report table.

I was trying to avoid proc report as its coding is more complex, and few people will be able to maintain it after I leave, and proc format as some totals only have one underlying number, and both the number and the total get formatted.

I thought there was a way to force some style attributes from the parent and give priority to either the row or the column parent, and was hoping someone would refresh my memory, but maybe am I wrong. 🙂

[ChrisNZ]

@Cynthia_sas Last ditch attempt, in case you have time Cynthia?

[Cynthia_sas]

Hi:
I think you're looking for STYLE_PRECEDENCE=ROW or STYLE_PRECEDENCE=COL -- goes on the TABLE statement after a slash (/).
Sorry, was not able to respond sooner.
Cynthia

[ChrisNZ]

@Cynthia_sas Yes that's what I meant! 

Sadly nothing seems to have worked. Even using the <parent> precedence doesn't provide a way.

It looks like proc report is the only option left.

It's a shame setting one style attribute for a column nullifies all attributes for rows.

[Cynthia_sas]

Hi:
It's still not clear to me what you want to do. You're not setting one attribute in the column dimension, you're setting 2 attributes. Your column dimension style override is impacting the HEIGHT column and only the HEIGHT column to be bold and to use the user-defined format for the font color. Your style override in the ROW dimension, for the ALL is doing a blanket override of both cells on the ALL row to be green, which is what is happening.
Do you only want the bold and green applied to the ALL row? Or how do you envision your overrides would work? That's what is not clear to me. I can't say that REPORT is the solution since I don't understand the desired result. Do you only want the 1184.40 total for HEIGHT to be green and you want the 1900.5 for WEIGHT to be black??? That's not what the ALL override is doing. That's why I'm confused.

Cynthia

[ChrisNZ]

Hi Cynthia,

I am the one confused now.

> Your column dimension style override is impacting the HEIGHT column and only the HEIGHT column to be bold and to use the user-defined format for the font color. 

I don't use a format. 

I just want the Height column to be bold and the total line to be green. And the intersection Height total to be both.

[Cynthia_sas · Accepted solution]

Hi:

  Oops, my bad. I didn't scroll back far enough to find your first post and I copied Ksharp's suggestion. I'm still not sure from your original code why you are adding bold just to the HEIGHT column, but I took it out for this example:

Unless you WANT the data cells in the HEIGHT column to be bold, it seemed to look better to me if only the summary line was bold and green. But again, maybe that's me not having a good picture of what you want. Actually, for the above example, I didn't really need style_precedence, because there was not any conflict -- but I can introduce a conflict that uses red and yellow to show exactly what will happen to the SUM of HEIGHT if I have a style override in the column dimension and a style override on the ALL in the row dimension:

proc tabulate data=SASHELP.CLASS;
  title 'default style_precedence=col but code explicitly';
  class AGE ;
  var WEIGHT  HEIGHT  ;
  table AGE all*[s=Header[background=white color=green]],
        WEIGHT*sum HEIGHT*sum*{s={color=red background=lightyellow}}
        / style_precedence=col;
run;

proc tabulate data=SASHELP.CLASS;
  title 'change style_precedence=row';
  class AGE ;
  var WEIGHT  HEIGHT  ;
  table AGE all*[s=Header[background=white color=green]],
        WEIGHT*sum HEIGHT*sum*{s={color=red background=lightyellow}}
        / style_precedence=row;
run;

But maybe this will give you some idea to start with. Here's what that output will look like:

So, still, I'm not entirely sure I understand what your desired result should look like. But  I hope that one of these examples will lead you to a solution.

Cynthia

[ChrisNZ]

Hi Cynthia,

Yes I want only one column in bold because that's the preferred column in my report.

The total line needs to be green (for example). And the preferred total should be green bold.

In your case you specify colour and background for both dimensions, so the overwrite that takes place makes sense: one attribute takes precedence.

In my example, I do not specify conflicting attributes yet both attributes are not used to complement each other: one set of attributes is completely ignored.