The SAS Output Delivery System and reporting techniques

Customizing a PROC REPORT table

Reply
Contributor
Posts: 54

Customizing a PROC REPORT table

Hello,

 

I have created a table using PROC REPORT that looks like this (reproduced in Excel with fake numbers):

 

image.png

 

 

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: 

 

image.png

 

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

Super User
Posts: 22,820

Re: Customizing a PROC REPORT table

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

 

 

Contributor
Posts: 54

Re: Customizing a PROC REPORT table

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

Super User
Posts: 22,820

Re: Customizing a PROC REPORT table

 

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.

 

 

Ask a Question
Discussion stats
  • 3 replies
  • 269 views
  • 0 likes
  • 2 in conversation