topic Re: Bold Line between Tabulate Classes? in ODS and Base Reporting

Bold Line between Tabulate Classes?

wcp_fnfg — Fri, 23 Jan 2015 20:40:40 GMT

In this example, I'd like to put a bold line between F, M, and All so as to break up the table more visually. These results will go to Excel using ods tagsets.excelxp.

proc tabulate data = sashelp.class missing;

class sex age;

var height;

table (sex='' all='Total')*(age=''),(height='Height' *mean='')/;

run;

Thanks!

Re: Bold Line between Tabulate Classes?

wcp_fnfg — Fri, 23 Jan 2015 21:00:42 GMT

Borrowing something from Cynthia, I can get the All to "highlight" which I guess is a fine alternative here, but while I have the style overrides for the ods on the vars, I can't get the "all" highlight to carry across.

proc tabulate data = combined missing order=data;

where &yyyymm. ge Fund_DT ge &yyyy.00 and not missing(hierarchy);

class hierarchy fico;

var count exc_cnt loan_amount exc_bal cnt_dlq30 bal_dlq30 netco active_ct exc_amt netco active_bal active_ct;

table (hierarchy='' all='Total')*

(fico=''

all={label='Total' s={background=#B8CCE4 font_weight=bold}}

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

(count = 'Units' *n='' * f=comma18.0 *[s=[tagattr='#,##0;[Red](#,##0);"-"']]

count = "% Total Volume (#)" *colpctsum='' *f=pctfmt24.2 *[s=[tagattr='0.00%;[Red]0.00%;"-"']]

exc_cnt = "% Exceptions (#)" *colpctsum='' *f=pctfmt24.2 *[s=[tagattr='0.00%;[Red]0.00%;"-"']]

loan_amount = "Loan Amount" *sum='' *f=comma24.0 *[s=[tagattr='$#,##0;[Red]($#,##0);"-"']]

loan_amount = "% Total Volume ($)" *colpctsum='' *f=pctfmt24.2 *[s=[tagattr='0.00%;[Red]0.00%;"-"']]

exc_amt = "% Exceptions ($)" *colpctsum<loan_amount>='' *f=pctfmt24.2 *[s=[tagattr='0.00%;[Red]0.00%;"-"']]

cnt_dlq30 = "30+ Rate (#)" *colpctsum<active_ct>='' *f=pctfmt24.2 *[s=[tagattr='0.00%;[Red]0.00%;"-"']]

bal_dlq30 = "30+ Rate ($)" *colpctsum<active_bal>='' *f=pctfmt24.2 *[s=[tagattr='0.00%;[Red]0.00%;"-"']]

netco = "Cum. Loss ($)" *colpctsum<loan_amount>='' *f=pctfmt24.2 *[s=[tagattr='0.00%;[Red]0.00%;"-"']])

box = "&yyyy. Loans with Exceptions" row=float;

format fico fico.;

run;

Re: Bold Line between Tabulate Classes?

ballardw — Fri, 23 Jan 2015 23:23:08 GMT

If banding by color is acceptable for your purpose this might be close

proc format library=work;
value $Sexc
'F' = 'White'
'M' = 'LightGreen'
;
run;
proc tabulate data = sashelp.class missing;
class sex /;
classlev sex /style={background=$sexc.};
class age /style=<parent>;
/*classlev sex /style={borderwidth=3};*/
classlev age /style=<parent>;
var height /style=<parent>;

table (sex=''*{style=<parent>} all*{style=<parent>})*(age=''),
(height='Height' *mean='')
/style_precedence=row;

run;

Which may be much easier.

Re: Bold Line between Tabulate Classes?

Cynthia_sas — Sat, 24 Jan 2015 01:00:56 GMT

Hi:

Good example, I believe that the OP's issue with the ALL not going across the row is that he needs to add style_precendence=row to his original code. My tendency is to build on his original example using SASHELP.CLASS and turn the single table (which is hard to get a "dividing line" the way he describes) into 3 separate tables on 1 page.

Cynthia

ods rtf file='c:\temp\table3.rtf' startpage=no;

ods pdf file='c:\temp\table3.pdf' startpage=no;

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

title 'Average Heights';

proc tabulate data=sashelp.class missing;

class sex age;

classlev age/style={width=1in};

var height;

table sex='Gender' all='Total',

age=' ',

height='Height'*mean=' ' / box=_page_;

run;

ods _all_ close;

Re: Bold Line between Tabulate Classes?

wcp_fnfg — Mon, 26 Jan 2015 13:43:55 GMT

Hey Cynthia, nice to see you again.

Unfortunately, because I have so many tables in this report, it won't be feasible to split the 1 table into three. For example, the second table in this report has 16 segments. Adding 16 extra lines (to space the report) would be a presentation issue.

I actually had to add an "all" row ( on the inside variable), so I'm currently trying to work out a way to either highlight just that row or put a bold line under it as a divider. I mentioned that in my reply to OP, but when I use the style override for All, I lose my tagattr on the vars.

Re: Bold Line between Tabulate Classes?

wcp_fnfg — Mon, 26 Jan 2015 15:03:32 GMT

This works to highlight everything, but I lose my Tagattr on the output for the all row.

proc tabulate data=sashelp.class missing;

class sex age;

var height;

table (sex='' all='Total')*

(age=''

all={label='Total NCO' s={background=yellow font_weight=bold}} *{s={font_weight=bold background=yellow }}),

(height='Height'*mean=' ' *[s=[tagattr='#,##0;[Red](#,##0);"-"']]

height='%height'*pctsum=''*[s=[tagattr='0.00%;[Red]0.00%;"-"']])

/ box=_page_ style_precedence=row;

run;