BookmarkSubscribeRSS Feed
wcp_fnfg
Obsidian | Level 7

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!

5 REPLIES 5
wcp_fnfg
Obsidian | Level 7

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;

ballardw
Super User

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.

Cynthia_sas
SAS Super FREQ

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;

wcp_fnfg
Obsidian | Level 7

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.

wcp_fnfg
Obsidian | Level 7

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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1671 views
  • 0 likes
  • 3 in conversation