topic Re: Proc tabulate: overlapping styles at line and row crossing in SAS Programming

tabulate: overlapping styles at line and row crossing

ChrisNZ — Tue, 15 Jun 2021 05:10:50 GMT

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 ;

Re: Proc tabulate: overlapping styles at line and row crossing

yabwon — Mon, 14 Jun 2021 09:44:17 GMT

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 ;

Re: Proc tabulate: overlapping styles at line and row crossing

ChrisNZ — Mon, 14 Jun 2021 10:47:52 GMT

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

Re: Proc tabulate: overlapping styles at line and row crossing

yabwon — Mon, 14 Jun 2021 11:17:23 GMT

[EDIT]

~~Yes, but I'm on 9.4M6.~~

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

Bart

Re: Proc tabulate: overlapping styles at line and row crossing

Ksharp — Mon, 14 Jun 2021 11:02:15 GMT

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;

Re: Proc tabulate: overlapping styles at line and row crossing

yabwon — Mon, 14 Jun 2021 11:03:05 GMT

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

Re: Proc tabulate: overlapping styles at line and row crossing

Ksharp — Mon, 14 Jun 2021 11:10:07 GMT

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 ;

Re: Proc tabulate: overlapping styles at line and row crossing

ChrisNZ — Tue, 15 Jun 2021 05:10:28 GMT

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. 🙂

Re: Proc tabulate: overlapping styles at line and row crossing

ChrisNZ — Wed, 16 Jun 2021 05:40:56 GMT

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

Re: Proc tabulate: overlapping styles at line and row crossing

Cynthia_sas — Wed, 16 Jun 2021 19:19:32 GMT

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

Re: Proc tabulate: overlapping styles at line and row crossing

ChrisNZ — Thu, 17 Jun 2021 01:53:32 GMT

@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.

Re: Proc tabulate: overlapping styles at line and row crossing

Cynthia_sas — Thu, 17 Jun 2021 15:41:47 GMT

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

Re: Proc tabulate: overlapping styles at line and row crossing

ChrisNZ — Thu, 17 Jun 2021 22:34:59 GMT

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.

Re: Proc tabulate: overlapping styles at line and row crossing

Cynthia_sas — Fri, 18 Jun 2021 01:47:58 GMT

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

Re: Proc tabulate: overlapping styles at line and row crossing

ChrisNZ — Fri, 18 Jun 2021 03:06:16 GMT

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.

Re: Proc tabulate: overlapping styles at line and row crossing

Cynthia_sas — Fri, 18 Jun 2021 15:47:25 GMT

Hi: PROC TABULATE basically replaces one set of attributes with the other in the event of conflicts between the row and the column style overrides. Typically, with TABULATE, you have to re-specify ALL the attributes you want to have in effect because TABULATE doesn't do inheritance the way you want. It is possible to get what you want with both REPORT and TABULATE, as shown below:

It's a bit more complicated using REPORT because AGE is numeric and you need to create a computed item to get a text string such as Total on the summary line. But the same results are do-able with either REPORT or TABULATE.

Cynthia

Re: Proc tabulate: overlapping styles at line and row crossing

ChrisNZ — Sun, 20 Jun 2021 09:13:29 GMT

Hi Cynthia,

Proc report allows merging styles (style/merge might be the default option?) while proc tabulate does not.

That's what I wanted to check.

proc report allows me to simply make a column with an attribute, a row with another and the crossing with both.

proc report data=SASHELP.CLASS;
  columns AGE WEIGHT HEIGHT ;
  define AGE      / group ;
  define  WEIGHT  / analysis;
  define  HEIGHT  / analysis style=[font_weight=bold];
  rbreak after    / summarize;
  compute after;
    call define(_row_,'style','style=[color=green]');
  endcomp;
run ;

There's no way with proc tabulate to have one row in green thin, one col in bold, and only one cell on green bold it seems.

Thank you for refreshing my memory about header and parent inheritance, and about precedence. 🙂

Re: Proc tabulate: overlapping styles at line and row crossing

Cynthia_sas — Mon, 21 Jun 2021 02:02:05 GMT

Hi:
Yes, REPORT and TABULATE have different inheritance rules.
Cynthia