The SAS Output Delivery System and reporting techniques

Format Total\all='' differently in Proc Tabulate?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 84
Accepted Solution

Format Total\all='' differently in Proc Tabulate?

In tabulate, any way to make my total line have a different background than the rest of the report or be bold or italics or any of those?  I can apply style to the class, but not just the all line.  Any thoughts, community?


Accepted Solutions
Solution
‎12-08-2014 05:34 PM
SAS Super FREQ
Posts: 8,742

Re: Format Total\all='' differently in Proc Tabulate?

Hi,

  Well, STYLE_PRECEDENCE=ROW fixes your #1 and makes it like #2. But my technique of using ALL={label='xxx' s={}} is what fixes your #3. See the screen shot for #3 in Excel. Once you move into the TABLE statement, you can separate out what you are applying to the row header vs what you are applying to the data cells. See the color coding for #3: the blue is applying to the label Total NCO and the green is applying to the data cells.
   

Cynthia

ods _all_ close;
ods tagsets.excelxp file='c:\something_new.xml'  style=normal;
  
title '1 *Nothing Highlights because 2nd Tag overrides';
proc tabulate data=sashelp.shoes missing;
where region in ("Africa",'Asia');
class region product;
var sales;
table (region='' all) * (product=''
  all='Total NCO'*{s={font_weight=bold background=lightblue tagattr='$#,##0.0,,;[Red]($#,##0.0,,);"-"' just=center}}),
   (sales='Sales' *sum=''*{s={font_weight=bold tagattr='$#,##0.0,,;[Red]($#,##0.0,,);"-"' just=center}})
/box = 'Style Override 1' style_precedence=row;
run;

title "3 Row Headers highlight with data, but also highlights Region\all";
proc tabulate data=sashelp.shoes missing;
where region in ("Africa",'Asia');
class region product  ;
var sales;
table (region='' all) * (product=''*{s={font_weight=bold tagattr='$#,##0.0,,;[Red]($#,##0.0,,);"-"' just=center}}
  all={label='Total NCO' s={background=lightblue font_weight=bold}}*{s={font_weight=bold background=lightblue tagattr='$#,##0.0,,;[Red]($#,##0.0,,);"-"' just=center}}),
   (sales='Sales' *sum='')
/box = {label='Style Override 3' s={width=2in}};
run;

ods _all_ close;
title;
footnote;


fix_1_and_3.png

View solution in original post


All Replies
SAS Super FREQ
Posts: 8,742

Re: Format Total\all='' differently in Proc Tabulate?

Hi, Look up the KEYWORD statement...that will change the style of the ALL line (but ALL of the ALL).

Otherwise, you need to put a style override in the TABLE statement. This is well documented in the TABULATE documentation and in our Report Writing class.

cynthia

Frequent Contributor
Posts: 84

Re: Format Total\all='' differently in Proc Tabulate?

Thanks for the reply.  The keyword statement has thus far only highlighted the All box and not the data to the right of it.     

SAS Super FREQ
Posts: 8,742

Re: Format Total\all='' differently in Proc Tabulate?

That is the reason you use the override in the TABLE statement. Not at a position to run code right now, but try this. It should work, barring typos.

Cynthia

ods _all_ close;

ods html file='c:\temp\something.html';

proc tabulate data=sashelp.class;

class age sex;

var height;

table age all*{s={font_weight=bold font_size=14pt background=yellow}},

      sex*height*(min max);

keyword all / style={background=pink font_size=14pt};

run;

ods html close;

Frequent Contributor
Posts: 84

Re: Format Total\all='' differently in Proc Tabulate?

Thanks, that got me close enough to figure it out.  It's a bummer than if I use two class\Alls, that the All category gets turned colors too, but I think I can sell it.     

Frequent Contributor
Posts: 84

Re: Format Total\all='' differently in Proc Tabulate?

Well it's close, but I need to use TagAttr on the data elements (var) and when I do that, the whole thing gets overwritten.  Seems I can have the Tagattr or I can have the highlights Smiley Sad

Gotta put the Tagattr in the style override for All and the Class

SAS Super FREQ
Posts: 8,742

Re: Format Total\all='' differently in Proc Tabulate?

Hi: CLASS only impacts the column header. I can't imagine why you would put TAGATTR in a CLASS statement. I can understand why you would want TAGATTR in the data cells. Worse case you should be able to specify TAGATTR in the TABLE statement for the ALL. You may need to specify STYLE_PRECEDENCE=ROW. But without more direction or an example it's hard to figure. You CAN have 2 ALLs and have them different. You just need to look more at the documentation.

Cynthia

ods _all_ close;
ods html file='c:\temp\something.html';
proc tabulate data=sashelp.class;
title '1) style_precedence=col is default';
class age sex;
var height;
table age all={label='Total's={background=lightgreen font_size=14pt}}*{s={font_weight=bold font_size=14pt background=yellow}},
      sex*height*(min max) all={label='Fred' s={background=cyan}}*{style={background=lightcyan}}*height*sum=' '
      /box={label='Box' s={background=pink}};
keyword all / style={background=pink font_size=14pt};
run;

  

proc tabulate data=sashelp.class;
title '2) style_precedence=row will change lower rh corner';
class age sex;
var height;
table age all={label='Total's={background=lightgreen font_size=14pt}}*{s={font_weight=bold font_size=14pt background=yellow}},
      sex*height*(min max) all={label='Fred' s={background=cyan}}*{style={background=lightcyan}}*height*sum=' '
      /box={label='Box' s={background=pink}} style_precedence=row;
keyword all / style={background=pink font_size=14pt};
run;

ods html close;

Frequent Contributor
Posts: 84

Re: Format Total\all='' differently in Proc Tabulate?

So I finally got a chance to draw up some examples here.  It's not that I want to get tagattr on the class, it's that to get it on the data, I need to tag not the statistics, but the class lines.  First example here will show that.

ods _all_ close;

ods tagsets.excelxp

file='c:\something2.xml'

  style=normal;

*Nothing Highlights because 2nd Tag overrides;

proc tabulate data=sashelp.shoes missing;

where region in ("Africa",'Asia');

class region product;

var sales;

table (region='' all) * (product=''

  all='Total NCO'*{s={font_weight=bold background=lightblue tagattr='$#,##0.0,,;[Red]($#,##0.0,,);"-"' just=center}}),

   (sales='Sales' *sum=''*{s={font_weight=bold tagattr='$#,##0.0,,;[Red]($#,##0.0,,);"-"' just=center}})

/box = 'Style Override 1';

/*keyword all / style={background=lightblue};*/

run;

*Data highlights, not the row headers (I like this one most);

proc tabulate data=sashelp.shoes missing;

where region in ("Africa",'Asia');

class region product;

var sales;

table (region='' all) * (product=''*{s={font_weight=bold tagattr='$#,##0.0,,;[Red]($#,##0.0,,);"-"' just=center}}

  all='Total NCO'*{s={font_weight=bold background=lightblue tagattr='$#,##0.0,,;[Red]($#,##0.0,,);"-"' just=center}}),

   (sales='Sales' *sum='')

/box = 'Style Override 2';

/*keyword all / style={background=lightblue};*/

run;

*Row Headers highlight with data, but also highlights Region\all;

proc tabulate data=sashelp.shoes missing;

where region in ("Africa",'Asia');

class region product;

var sales;

table (region='' all) * (product=''*{s={font_weight=bold tagattr='$#,##0.0,,;[Red]($#,##0.0,,);"-"' just=center}}

  all='Total NCO'*{s={font_weight=bold background=lightblue tagattr='$#,##0.0,,;[Red]($#,##0.0,,);"-"' just=center}}),

   (sales='Sales' *sum='')

/box = 'Style Override 3';

keyword all / style={background=lightblue};

run;

ods tagsets.excelxp close;

Solution
‎12-08-2014 05:34 PM
SAS Super FREQ
Posts: 8,742

Re: Format Total\all='' differently in Proc Tabulate?

Hi,

  Well, STYLE_PRECEDENCE=ROW fixes your #1 and makes it like #2. But my technique of using ALL={label='xxx' s={}} is what fixes your #3. See the screen shot for #3 in Excel. Once you move into the TABLE statement, you can separate out what you are applying to the row header vs what you are applying to the data cells. See the color coding for #3: the blue is applying to the label Total NCO and the green is applying to the data cells.
   

Cynthia

ods _all_ close;
ods tagsets.excelxp file='c:\something_new.xml'  style=normal;
  
title '1 *Nothing Highlights because 2nd Tag overrides';
proc tabulate data=sashelp.shoes missing;
where region in ("Africa",'Asia');
class region product;
var sales;
table (region='' all) * (product=''
  all='Total NCO'*{s={font_weight=bold background=lightblue tagattr='$#,##0.0,,;[Red]($#,##0.0,,);"-"' just=center}}),
   (sales='Sales' *sum=''*{s={font_weight=bold tagattr='$#,##0.0,,;[Red]($#,##0.0,,);"-"' just=center}})
/box = 'Style Override 1' style_precedence=row;
run;

title "3 Row Headers highlight with data, but also highlights Region\all";
proc tabulate data=sashelp.shoes missing;
where region in ("Africa",'Asia');
class region product  ;
var sales;
table (region='' all) * (product=''*{s={font_weight=bold tagattr='$#,##0.0,,;[Red]($#,##0.0,,);"-"' just=center}}
  all={label='Total NCO' s={background=lightblue font_weight=bold}}*{s={font_weight=bold background=lightblue tagattr='$#,##0.0,,;[Red]($#,##0.0,,);"-"' just=center}}),
   (sales='Sales' *sum='')
/box = {label='Style Override 3' s={width=2in}};
run;

ods _all_ close;
title;
footnote;


fix_1_and_3.png
Frequent Contributor
Posts: 84

Re: Format Total\all='' differently in Proc Tabulate?

That's wild.  Have a green star.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 1451 views
  • 3 likes
  • 2 in conversation