BookmarkSubscribeRSS Feed
Jack1
Obsidian | Level 7

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

3 REPLIES 3
Reeza
Super User

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

 

 

Jack1
Obsidian | Level 7

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

Reeza
Super User

 

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.

 

 

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1185 views
  • 1 like
  • 2 in conversation