topic Re: color cells based on other cells values-proc report in SAS Programming

color cells based on other cells values-proc report

Ronein — Sun, 17 Aug 2025 13:52:47 GMT

Hello friends,

I want to use proc report.

I wan to color the dif columns based on same cell value.
Here It was done 100% well.
If dif_P10>0.05 then I want to color dif_P10 in red
If dif_P20>0.05 then I want to color dif_P20 in red
If dif_P30>0.05 then I want to color dif_P30 in red

I also want to color cells based on other cells values.

If dif_P10>0.05 then I want to color P10 in red

If dif_P20>0.05 then I want to color P20 in red

If dif_P30>0.05 then I want to color P30 in red

and so on

Here I dont know how to do it.

Can anyone help please?

I tried use code with column location but it didnt work



		data have;
		input P_IND_Prob mon  P10 P20 P30 P40 P50 P60 P70 P80 P90;
		cards;
		0 202507 0.1 0.15 0.25 0.31 0.49 0.46 0.6 0.75 0.8 0.97
		0 202506 0.08 0.13 0.14 0.44 0.52 0.65 0.74 0.82 0.91 0.94
		;
		run;
		proc sort data=have;
		by mon;
		run;

		data have2;
		set have;
		by mon;
		dif_p10 =abs(p10-lag(p10));
		dif_p20 =abs(p20-lag(p20)); 
		dif_p30 =abs(p30-lag(p30)); 
		dif_p40 =abs(p40-lag(p40)); 
		dif_p50 =abs(p50-lag(p50));
		dif_p60 =abs(p60-lag(p60)); 
		dif_p70 =abs(p70-lag(p70));
		dif_p80 =abs(p80-lag(p80));
		dif_p90 =abs(p90-lag(p90));
		run;


Proc format ;
value highlight_S4_Fmt
0.05<-high = 'red'
;
Run;

proc report data=have2 missing nowd  style(report)={frame=box font_size=8pt  bordercolor=black borderwidth=2px} ;
column 
mon
P10
P20
P30
P40
P50
P60
P70
P80
P90
dif_p10
dif_p20
dif_p30
dif_p40
dif_p50
dif_p60
dif_p70
dif_p80
dif_p90
;
define   mon/DISPLAY  ;
define   P10/DISPLAY   f=percent10.4 ;
define   P20/DISPLAY  f=percent10.4;
define   P30/DISPLAY   f=percent10.4;
define   P40/DISPLAY   f=percent10.4;
define   P50/DISPLAY   f=percent10.4;
define   P60/DISPLAY  f=percent10.4;
define   P70/DISPLAY  f=percent10.4;
define   P80/DISPLAY  f=percent10.4;
define   P90/DISPLAY  f=percent10.4;
define   dif_p10/DISPLAY f=percent10.4   style=[background=highlight_S4_Fmt.];
define   dif_p20/DISPLAY f=percent10.4   style=[background=highlight_S4_Fmt.];
define   dif_p30/DISPLAY f=percent10.4   style=[background=highlight_S4_Fmt.];
define   dif_p40/DISPLAY  f=percent10.4   style=[background=highlight_S4_Fmt.];
define   dif_p50/DISPLAY  f=percent10.4   style=[background=highlight_S4_Fmt.];
define   dif_p60/DISPLAY  f=percent10.4   style=[background=highlight_S4_Fmt.];
define   dif_p70/DISPLAY f=percent10.4   style=[background=highlight_S4_Fmt.];
define   dif_p80/DISPLAY  f=percent10.4   style=[background=highlight_S4_Fmt.];
define   dif_p90/DISPLAY  f=percent10.4   style=[background=highlight_S4_Fmt.];
Run;

Re: color cells based on other cells values-proc report

Ronein — Sun, 17 Aug 2025 14:18:15 GMT

tried this code but it didnt work!

Dif columsn are not colored based on the criteria

proc report data=have2 missing nowd  style(report)={frame=box font_size=8pt  bordercolor=black borderwidth=2px} ;
column 
mon
P10
P20
P30
P40
P50
P60
P70
P80
P90
dif_p10
dif_p20
dif_p30
dif_p40
dif_p50
dif_p60
dif_p70
dif_p80
dif_p90
;
define   mon/DISPLAY  ;
define   P10/DISPLAY   f=percent10.4 ;
define   P20/DISPLAY  f=percent10.4;
define   P30/DISPLAY   f=percent10.4;
define   P40/DISPLAY   f=percent10.4;
define   P50/DISPLAY   f=percent10.4;
define   P60/DISPLAY  f=percent10.4;
define   P70/DISPLAY  f=percent10.4;
define   P80/DISPLAY  f=percent10.4;
define   P90/DISPLAY  f=percent10.4;
define   dif_p10/DISPLAY f=percent10.4   style=[background=highlight_S4_Fmt.];
define   dif_p20/DISPLAY f=percent10.4   style=[background=highlight_S4_Fmt.];
define   dif_p30/DISPLAY f=percent10.4   style=[background=highlight_S4_Fmt.];
define   dif_p40/DISPLAY  f=percent10.4   style=[background=highlight_S4_Fmt.];
define   dif_p50/DISPLAY  f=percent10.4   style=[background=highlight_S4_Fmt.];
define   dif_p60/DISPLAY  f=percent10.4   style=[background=highlight_S4_Fmt.];
define   dif_p70/DISPLAY f=percent10.4   style=[background=highlight_S4_Fmt.];
define   dif_p80/DISPLAY  f=percent10.4   style=[background=highlight_S4_Fmt.];
define   dif_p90/DISPLAY  f=percent10.4   style=[background=highlight_S4_Fmt.];
	compute P10;
	if dif_p10>0.05 then  	call define (_col_,"style", "style={background=red}");
	endcomp;

	compute P20;
	if dif_p20>0.05 then  	call define (_col_,"style", "style={background=red}");
	endcomp;

	compute P30;
	if dif_p30>0.05 then  	call define (_col_,"style", "style={background=red}");
	endcomp;

	compute P40;
	if dif_p40>0.05 then  	call define (_col_,"style", "style={background=red}");
	endcomp;

	compute P50;
	if dif_p50>0.05 then  	call define (_col_,"style", "style={background=red}");
	endcomp;

	compute P60;
	if dif_p60>0.05 then  	call define (_col_,"style", "style={background=red}");
	endcomp;

	compute P70;
	if dif_p70>0.05 then  	call define (_col_,"style", "style={background=red}");
	endcomp;

	compute P80;
	if dif_p80>0.05 then  	call define (_col_,"style", "style={background=red}");
	endcomp;

	compute P90;
	if dif_p90>0.05 then  	call define (_col_,"style", "style={background=red}");
	endcomp;
	Run;

Re: color cells based on other cells values-proc report

Ksharp — Mon, 18 Aug 2025 01:00:08 GMT

PROC REPORT scan table from left to right.

So when you want to refer to left column base on right column, you need use right column within COMPUTE block.

		data have;
		input P_IND_Prob mon  P10 P20 P30 P40 P50 P60 P70 P80 P90;
		cards;
		0 202507 0.1 0.15 0.25 0.31 0.49 0.46 0.6 0.75 0.8 0.97
		0 202506 0.08 0.13 0.14 0.44 0.52 0.65 0.74 0.82 0.91 0.94
		;
		run;
		proc sort data=have;
		by mon;
		run;

		data have2;
		set have;
		by mon;
		dif_p10 =abs(p10-lag(p10));
		dif_p20 =abs(p20-lag(p20)); 
		dif_p30 =abs(p30-lag(p30)); 
		dif_p40 =abs(p40-lag(p40)); 
		dif_p50 =abs(p50-lag(p50));
		dif_p60 =abs(p60-lag(p60)); 
		dif_p70 =abs(p70-lag(p70));
		dif_p80 =abs(p80-lag(p80));
		dif_p90 =abs(p90-lag(p90));
		run;


Proc format ;
value highlight_S4_Fmt
0.05<-high = 'red'
;
Run;
proc report data=have2 missing nowd  style(report)={frame=box font_size=8pt  bordercolor=black borderwidth=2px} ;
column 
mon
P10
P20
P30
P40
P50
P60
P70
P80
P90
dif_p10
dif_p20
dif_p30
dif_p40
dif_p50
dif_p60
dif_p70
dif_p80
dif_p90
;
define   mon/DISPLAY  ;
define   P10/DISPLAY   f=percent10.4 ;
define   P20/DISPLAY  f=percent10.4;
define   P30/DISPLAY   f=percent10.4;
define   P40/DISPLAY   f=percent10.4;
define   P50/DISPLAY   f=percent10.4;
define   P60/DISPLAY  f=percent10.4;
define   P70/DISPLAY  f=percent10.4;
define   P80/DISPLAY  f=percent10.4;
define   P90/DISPLAY  f=percent10.4;
define   dif_p10/DISPLAY f=percent10.4   style=[background=highlight_S4_Fmt.];
define   dif_p20/DISPLAY f=percent10.4   style=[background=highlight_S4_Fmt.];
define   dif_p30/DISPLAY f=percent10.4   style=[background=highlight_S4_Fmt.];
define   dif_p40/DISPLAY  f=percent10.4   style=[background=highlight_S4_Fmt.];
define   dif_p50/DISPLAY  f=percent10.4   style=[background=highlight_S4_Fmt.];
define   dif_p60/DISPLAY  f=percent10.4   style=[background=highlight_S4_Fmt.];
define   dif_p70/DISPLAY f=percent10.4   style=[background=highlight_S4_Fmt.];
define   dif_p80/DISPLAY  f=percent10.4   style=[background=highlight_S4_Fmt.];
define   dif_p90/DISPLAY  f=percent10.4   style=[background=highlight_S4_Fmt.];
	compute dif_p90;
	if dif_p10>0.05 then  	call define ('P10',"style", "style={background=red}");
	if dif_p20>0.05 then  	call define ('P20',"style", "style={background=red}");
	if dif_p30>0.05 then  	call define ('P30',"style", "style={background=red}");
	if dif_p40>0.05 then  	call define ('P40',"style", "style={background=red}");
	if dif_p50>0.05 then  	call define ('P50',"style", "style={background=red}");
	if dif_p60>0.05 then  	call define ('P60',"style", "style={background=red}");
	if dif_p70>0.05 then  	call define ('P70',"style", "style={background=red}");
	if dif_p80>0.05 then  	call define ('P80',"style", "style={background=red}");
	if dif_p90>0.05 then  	call define ('P90',"style", "style={background=red}");
	endcomp;
	Run;

Re: color cells based on other cells values-proc report

Ronein — Mon, 18 Aug 2025 04:49:13 GMT

As I understand you wrote

compute dif_p90;

but you can type here any variable that appear in right of all dif variables .

I didnt see any information to read about this rule -when you want to refer to left column base on right column, you need use right column within COMPUTE block

Re: color cells based on other cells values-proc report

Ksharp — Mon, 18 Aug 2025 05:10:12 GMT

Yes. You are right.
As long as the variable in COMPUTE
compute dif_p90;
is at the right side of the variable you refer to (e.x. p90)

Re: color cells based on other cells values-proc report

Ksharp — Mon, 18 Aug 2025 05:11:14 GMT

"I didnt see any information to read about this rule "
Why not make an example to test this rule ?

Re: color cells based on other cells values-proc report

Ronein — Mon, 18 Aug 2025 07:54:11 GMT

And If there is no any variable on the right?

For example:

If variables order is - IND MON dif_P10 dif_ P20 dif_P30 P10 P20 P30

And I want to -

color P10 bases on dif_p10

color P20 bases on dif_p20

color P30 bases on dif_p30

But as you can see-

dif_p10 is left to P10

dif_p20 is left to P20

dif_P30 is left to p30

Re: color cells based on other cells values-proc report

Ronein — Mon, 18 Aug 2025 07:54:44 GMT

I see that the rule is true but I want to read a bit about it

Re: color cells based on other cells values-proc report

Ksharp — Mon, 18 Aug 2025 08:13:44 GMT

Then use the right most variable P30:

compute P30;
if dif_p10>0.05 then call define ('P10',"style", "style={background=red}");

Re: color cells based on other cells values-proc report

Kathryn_SAS — Mon, 18 Aug 2025 11:31:28 GMT

The following is in the documentation under Required arguments:

report-item
specifies a data set variable, a computed variable, or a statistic to associate the compute block with. You must include the report item in the COLUMN statement. If the item is a computed variable, then you must include a DEFINE statement for it.
Note The position of a computed variable is important. PROC REPORT assigns values to the columns in a row of a report from left to right. Consequently, you cannot base the calculation of a computed variable on any variable that appears to its right in the report.

I would also suggest the following paper:

https://support.sas.com/resources/papers/proceedings15/SAS1642-2015.pdf