Proc Report - style conditionally

[ChrisWoo]

Just trying to learn proc report, and i dont know how to style my cell conditionally..

Any ways to color my cell (background=yellow) when MSRP / Invoice > 100k AND,

change font color (color=red) when MSRP / Invoice > 1 Mil ?

Appreciate your help.

Sample:

Here my code:

proc report data=sashelp.cars list spanrows;
	title "This is a complete report";
	columns ("World Vehicle" origin make) drivetrain ,(msrp invoice);
	format msrp invoice dollar18.2;
	
	define Origin / group center;
	define Make / group left;
	define DriveTrain / across left;

	break after origin / summarize style=[fontweight=bold];
	
    compute MSRP;
       if MSRP > 100000  then
       call define(_col_, "style", "style={backgroundcolor=yellow}");
    endcomp;
	
    compute after origin;
        line " ";
    endcomp;
run;

[Kathryn_SAS]

When you refer to variables under an Across, you need to refer to them by the column position, _Cn_. You can simplify this by using macro logic. Try the following:

%macro loop;
ods listing close;
ods excel file='c:\temp\test.xlsx';

proc report data=sashelp.cars list spanrows;
	title "This is a complete report";
	columns ("World Vehicle" origin make) drivetrain ,(msrp invoice) dummy;
	format msrp invoice dollar18.2;
	
	define Origin / group center;
	define Make / group left;
	define DriveTrain / across left;
	define dummy / computed noprint;

	break after origin / summarize style=[fontweight=bold];
	
    compute dummy;
	 %do i=3 %to 8;
       if _c&i._ > 100000  then
       call define("_c&i._", "style", "style={backgroundcolor=yellow}");
       if _c&i._ > 1000000  then
       call define("_c&i._", "style/merge", "style={foreground=red}");
	 %end;
    endcomp;
	
    compute after origin;
        line " ";
    endcomp;
run;

ods excel close;
ods listing;
title;
%mend;
%loop

[Ksharp]

The most convenient way is using Traffic Light trick.

proc format;
value background
100000<-high='yellow' 
;

value foreground
100000<-high='red' 
;
run;


proc report data=sashelp.cars list spanrows nowd;
	title "This is a complete report";
	columns ("World Vehicle" origin make) drivetrain ,(msrp invoice);
	format msrp invoice dollar18.2;
	define Origin / group center;
	define Make / group left;
	define DriveTrain / across left;
	define msrp/analysis sum style={background=background. foreground=foreground.};
	define invoice/analysis sum style={background=background. foreground=foreground.};

	break after origin / summarize style=[fontweight=bold];
		
    compute after origin;
        line " ";
    endcomp;
run;

[ballardw]

---

@ChrisWoo wrote:

Any ways to color my cell (background=yellow) when MSRP / Invoice > 100k AND,

change font color (color=red) when MSRP / Invoice > 1 Mil ?

Did anyone else reading this ask themselves what is to be highlighted because of that division calculation?

ChrisWoo, for questions related to programming you may want to use words like "or" or "and" as appropriate instead of symbols like / * + - that may be interpreted as arithmetic operators.