- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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!
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks Art...was finally able to get back to this project and that was very helpful!!!