Customizing a PROC REPORT table

Jack1 — Thu, 09 Nov 2017 21:53:49 GMT

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

Re: Customizing a PROC REPORT table

Reeza — Thu, 09 Nov 2017 22:18:14 GMT

I don't think you want the style applied to the full column, just the cell?

Re: Customizing a PROC REPORT table

Jack1 — Thu, 09 Nov 2017 22:21:37 GMT

Just the cells that show a YoY drop to be red, YoY gain to be green.

Re: Customizing a PROC REPORT table

Reeza — Thu, 09 Nov 2017 23:53:44 GMT

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.

topic Customizing a PROC REPORT table in ODS and Base Reporting

Customizing a PROC REPORT table

Re: Customizing a PROC REPORT table

Re: Customizing a PROC REPORT table

Re: Customizing a PROC REPORT table