topic Re: Format Total\all='' differently in Proc Tabulate? in ODS and Base Reporting

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

wcp_fnfg — Thu, 04 Dec 2014 16:07:01 GMT

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?

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

Cynthia_sas — Thu, 04 Dec 2014 18:52:23 GMT

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

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

wcp_fnfg — Thu, 04 Dec 2014 18:54:09 GMT

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

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

Cynthia_sas — Thu, 04 Dec 2014 19:01:30 GMT

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;

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

wcp_fnfg — Thu, 04 Dec 2014 19:09:10 GMT

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.

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

wcp_fnfg — Thu, 04 Dec 2014 19:14:12 GMT

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

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

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

Cynthia_sas — Thu, 04 Dec 2014 23:20:15 GMT

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;

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

wcp_fnfg — Mon, 08 Dec 2014 19:47:48 GMT

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;

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

Cynthia_sas — Mon, 08 Dec 2014 22:34:28 GMT

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;

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

wcp_fnfg — Tue, 09 Dec 2014 13:44:55 GMT

That's wild. Have a green star.