BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
teg_76
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

8 REPLIES 8
art297
Opal | Level 21

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

Ksharp
Super User

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

teg_76
Calcite | Level 5

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

Reeza
Super User

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.

Cynthia_sas
SAS Super FREQ

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;

art297
Opal | Level 21

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;

teg_76
Calcite | Level 5

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.

teg_76
Calcite | Level 5

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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 8 replies
  • 8868 views
  • 0 likes
  • 5 in conversation