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!
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;
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
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
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
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.
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;
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;
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.
Thanks Art...was finally able to get back to this project and that was very helpful!!!
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 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.