Hello,
I have created a table using PROC REPORT that looks like this (reproduced in Excel with fake numbers):
This is the code I used:
ods listing close;
options printerpath=png nodate nonumber papersize=('9.0in','8.0in') leftmargin=0.5in rightmargin=0.5in bottommargin=0.65in;
ods _all_ close ;
ods printer file="c:/user/homareau/SAS_SQL/QBR/ip_gainers_decliners.png" style=sasweb ;
proc report data=gain_decl nowd spanrows center;
column ('FY18Q1 IP Gainers & Decliners by ADV' impact customer NAICS_bucket naics_desc YoYChg Contribution);
define Impact / "Impact" group style=[FONTWEIGHT=BOLD];
define customer / "Customer Name" display;
define naics_bucket / "Customer's / Industry Code" display;
define YoYChg / "ADV YoY" display sum format=NEGPAREN12.0 ;
define naics_desc / "Description of / Customer's Industry" display ;
*define contribution / "Contribution /to YoY" display;
break after impact / dol skip summarize suppress style=[font_size=10];
Compute after impact;
Line "";
endcomp;
run;
ods printer close;
ods listing;
No problem.
Now I want to color code the 'ADV YoY' column so that negative values are red, positive are green. However the figures are coming out red in this column, regardless of my compute statement:
ods listing close;
options printerpath=png nodate nonumber papersize=('9.0in','8.0in') leftmargin=0.5in rightmargin=0.5in bottommargin=0.65in;
ods _all_ close ;
ods printer file="c:/user/homareau/SAS_SQL/QBR/ip_gainers_decliners.png" style=sasweb ;
proc report data=gain_decl nowd spanrows center;
column ('FY18Q1 IP Gainers & Decliners by ADV' impact customer NAICS_bucket naics_desc YoYChg Contribution);
define Impact / "Impact" group style=[FONTWEIGHT=BOLD];
define customer / "Customer Name" display;
define naics_bucket / "Customer's / Industry Code" display;
define YoYChg / "ADV YoY" display sum format=NEGPAREN12.0 ;
define naics_desc / "Description of / Customer's Industry" display ;
*define contribution / "Contribution /to YoY" display;
break after impact / dol skip summarize suppress style=[font_size=10];
Compute after impact;
Line "";
endcomp;
* Turns negative YOY values to red;
compute YoYChg;
if sign(YoYChg) < 0 then call define(_col_,'style','style=[color=RED]' );
if sign(YoYChg) > 0 then call define(_col_,'style','style=[color=GREEN]');
endcomp;
run;
ods printer close;
ods listing;
This is what I end up with:
Three Questions:
1. How do I fix my code so in the 'ADV YoY' column to show gainers are green and decliners are red?
2. Can I adjust the size of my title font 'FY18Q1 IP Gainers & Decliners by ADV' to be larger than it is?
3. In the rows with the totals there are 4 empty cells. Can I merge them and put text that says something like 'Total of Gainers' and 'Total of Decliners' for each of the categories?
Thanks
Jack
I don't think you want the style applied to the full column, just the cell?
Just the cells that show a YoY drop to be red, YoY gain to be green.
1. How do I fix my code so in the 'ADV YoY' column to show gainers are green and decliners are red?
Use a custom format instead. See page 8 of here:
http://support.sas.com/resources/papers/proceedings11/290-2011.pdf
2. Can I adjust the size of my title font 'FY18Q1 IP Gainers & Decliners by ADV' to be larger than it is?
Yes, you can definitely make it larger.
3. In the rows with the totals there are 4 empty cells. Can I merge them and put text that says something like 'Total of Gainers' and 'Total of Decliners' for each of the categories?
I don't know if that's exactly possible but you could customize those last lines for sure to have the totals shown in a single cell.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.