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

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

teg_76 — Thu, 16 Feb 2012 01:09:11 GMT

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?

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

Thank you!

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

art297 — Thu, 16 Feb 2012 01:16:15 GMT

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

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

Ksharp — Thu, 16 Feb 2012 02:50:59 GMT

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

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

teg_76 — Thu, 16 Feb 2012 16:42:44 GMT

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	A	B	C	D	E
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

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

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

Reeza — Thu, 16 Feb 2012 17:14:32 GMT

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.

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

Cynthia_sas — Thu, 16 Feb 2012 17:53:57 GMT

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;

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

art297 — Thu, 16 Feb 2012 18:10:38 GMT

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;

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

teg_76 — Fri, 17 Feb 2012 04:54:05 GMT

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.

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

teg_76 — Tue, 20 Mar 2012 16:14:58 GMT

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