Help using Base SAS procedures

Bold an entire row based on value of one variable in row?

Accepted Solution Solved
Reply
Contributor
Posts: 41
Accepted Solution

Bold an entire row based on value of one variable in row?

Hi,

I'm using PROC FORMAT and PROC PRINT to produce a report.  I was wondering if there is a way to bold and underline an entire row based on the value of a variable in the row.

Basically I'm doing a profit and loss statement and my data is in the format below.  I'd like to bold the whole total revenue and total expenses row.  Is there a condition I can use to bold those rows, such as, if Category=Total Revenue then bold entire row.

If not, Is there some standard code out there for formating profit and loss statement when I'm starting with a dataset that looks like the one below?

CATEGORYABCDE
Revenue54663
Supplemental Revenue65544
Total Revenue11911107
Expenses75673
Supplemental Expenses65544
Total Expenses131011117

Thank you!


Accepted Solutions
Solution
‎02-16-2012 01:10 PM
PROC Star
Posts: 7,363

Re: Bold an entire row based on value of one variable in row?

Cynthia can probably expand on this to show how you could do what you want without having to do any preprocessing of your data, but I think this is the general idea.

However, you can only set the style with certain ods destinations.  I used html:

data have;

  informat category $21.;

  input CATEGORY & A B C D E;

  cards;

Revenue  5 4 6 6 3

Supplemental Revenue  6 5 5 4 4

Expenses  7 5 6 7 3

Supplemental Expenses  6 5 5 4 4

;

data need;

  set have;

  if index(category,'Revenue') then main_category=1;

  else main_category=2;

run;

ods html file='c:\want.html';

proc report data=need nowindows missing headline;

  column main_category category a--e;

  define main_category / group noprint;

  define category / group;

  break after main_category / summarize skip;

  compute category;

    if _break_ = 'main_category' then do;

      if main_category eq 1 then category = 'Total Revenue';

       else category = 'Total Expenses';

      call define(_row_,'style','style={font_weight=bold}');

    end;

  endcomp;

run;

ods html close;

View solution in original post


All Replies
PROC Star
Posts: 7,363

Bold an entire row based on value of one variable in row?

If you can define the specifics in terms of variable values, I don't see why you couldn't combine proc format and proc print to do what you want.  It would just be a form of traffic lighting but, rather than changing colors, you change the font attributes.

Take a look at: http://www2.sas.com/proceedings/sugi26/p003-26.pdf

Super User
Posts: 9,687

Bold an entire row based on value of one variable in row?

For your situation, the best choice is useing proc report.

Although proc print also can use Traffic Lighting, it is not suited for your question.

Ksharp

Contributor
Posts: 41

Bold an entire row based on value of one variable in row?

Hi Ksharp,

Yeah it looks like Traffic Lighting won't work for bolding a whole row based on the value of a variable in that row.  So can proc report take data that looks like this:

CATEGORY
ABCDE
Revenue54663
Supplemental Revenue65544
Expenses75673
Supplemental Expenses65544

and make it look like this?

CATEGORY                                          A             B             C             D             E

                                                                               

Revenue                                              5              4              6              6              3

Supplemental Revenue                         6              5              5              4              4

Total Revenue                                   11            9              11           10              7

                                                                               

Expenses                                             7              5              6              7              3

Supplemental Expenses                        6              5              5              4              4

Total Expenses                                  13           10              11           11              7

Super User
Posts: 17,868

Bold an entire row based on value of one variable in row?

Yes, your method might vary a bit based on your ODS destination however.

Here's the example for ExcelXP

http://support.sas.com/resources/papers/proceedings10/153-2010.pdf

Cynthia has posted extensive examples and details on how to do this on these boards as well so with a bit of digging you should find an example specific to your destination.

SAS Super FREQ
Posts: 8,743

Re: Bold an entire row based on value of one variable in row?

Thanks for pointing to previous forum postings!

I did have this example -- somewhat different that shows 3 possible ways to do row level highlighting.

cynthia

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

  proc report data=sashelp.shoes nowd;

    where product contains 'Dress' and

          region contains 'Europe';

    title '1) Style Override on BREAK statement';

    column region product sales inventory returns;

    define region /group;

    define product / group;

    define sales /sum;

    define inventory / sum;

    define returns / sum;

    break after region / summarize

          style={font_weight=bold font_style=roman};

  run;

         

  proc report data=sashelp.shoes nowd;

    where product contains 'Dress' and

          region contains 'Europe';

    title '2) Style Override in COMPUTE block';

    column region product sales inventory returns;

    define region /group;

    define product / group;

    define sales /sum;

    define inventory / sum;

    define returns / sum;

    compute region;

      if upcase(_break_) = 'REGION' then do;

        region = catx(' ','Total',region);

        call define(_row_,'style','style={font_weight=bold}');

      end;

    endcomp;

    break after region / summarize;

  run;

   

    proc report data=sashelp.shoes nowd;

    where product contains 'Dress' and

          region contains 'Europe';

    title '3) Highlight based on Other Variable value';

    column region product sales inventory returns;

    define region /group;

    define product / group;

    define sales /sum;

    define inventory / sum;

    define returns / sum;

    compute sales;

      ** change break line and title;

      if upcase(_break_) = 'REGION' then do;

        if region='Western Europe' then

          call define(_row_,'style','style={font_weight=bold background=yellow}');

        else if region='Eastern Europe' then

          call define(_row_,'style','style={font_weight=bold background=cyan}');

        region = catx(' ','Total',region);

      end;

      ** highlight row based on product value;

      if findw(product, "Women's") gt 0 then

         call define(_row_,'style','style={background=pink}');

      else if findw(product, "Men's") gt 0 then

         call define(_row_,'style','style={background=lightblue}');

    endcomp;

    break after region / summarize;

  run;

ods html close;

Solution
‎02-16-2012 01:10 PM
PROC Star
Posts: 7,363

Re: Bold an entire row based on value of one variable in row?

Cynthia can probably expand on this to show how you could do what you want without having to do any preprocessing of your data, but I think this is the general idea.

However, you can only set the style with certain ods destinations.  I used html:

data have;

  informat category $21.;

  input CATEGORY & A B C D E;

  cards;

Revenue  5 4 6 6 3

Supplemental Revenue  6 5 5 4 4

Expenses  7 5 6 7 3

Supplemental Expenses  6 5 5 4 4

;

data need;

  set have;

  if index(category,'Revenue') then main_category=1;

  else main_category=2;

run;

ods html file='c:\want.html';

proc report data=need nowindows missing headline;

  column main_category category a--e;

  define main_category / group noprint;

  define category / group;

  break after main_category / summarize skip;

  compute category;

    if _break_ = 'main_category' then do;

      if main_category eq 1 then category = 'Total Revenue';

       else category = 'Total Expenses';

      call define(_row_,'style','style={font_weight=bold}');

    end;

  endcomp;

run;

ods html close;

Contributor
Posts: 41

Bold an entire row based on value of one variable in row?

Thank you Reeza, Cynthia and Art.  That was all extremely helpful to read.  I haven't had a chance to try the code out yet but I will post my results as soon as I do.

This is great stuff!  Thanks again.

Contributor
Posts: 41

Bold an entire row based on value of one variable in row?

Thanks Art...was finally able to get back to this project and that was very helpful!!!   Smiley Happy

☑ This topic is SOLVED.

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

Discussion stats
  • 8 replies
  • 794 views
  • 0 likes
  • 5 in conversation