topic Proc Report - style conditionally in ODS and Base Reporting

Proc Report - style conditionally

ChrisWoo — Mon, 01 Dec 2025 17:39:54 GMT

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;

Re: Proc Report - style conditionally

Kathryn_SAS — Mon, 01 Dec 2025 18:14:46 GMT

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

Re: Proc Report - style conditionally

Ksharp — Tue, 02 Dec 2025 07:34:03 GMT

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;

Re: Proc Report - style conditionally

ballardw — Wed, 03 Dec 2025 22:29:30 GMT

@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.

Re: Proc Report - style conditionally

ChrisWoo — Fri, 05 Dec 2025 05:08:51 GMT

I will keep it in mind, thanks