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!
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;
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.
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;
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.
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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.