cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
Ronein
Onyx | Level 15 Ronein
Onyx | Level 15

color cells based on other cells values-proc report

Posted 2 weeks ago (365 views)

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;
0 Likes
Reply
9 REPLIES 9
Ronein
Onyx | Level 15 Ronein
Onyx | Level 15

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

Posted 2 weeks ago (350 views)  |   In reply to Ronein

 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;
0 Likes
Reply
Ksharp
Super User Ksharp
Super User

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

Posted 2 weeks ago (309 views)  |   In reply to Ronein

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;

Ksharp_0-1755478798525.png

 

0 Likes
Reply
Ronein
Onyx | Level 15 Ronein
Onyx | Level 15

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

Posted 2 weeks ago (290 views)  |   In reply to Ksharp

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

0 Likes
Reply
Ksharp
Super User Ksharp
Super User

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

Posted 2 weeks ago (283 views)  |   In reply to Ronein
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)
0 Likes
Reply
Ronein
Onyx | Level 15 Ronein
Onyx | Level 15

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

Posted 2 weeks ago (266 views)  |   In reply to Ksharp

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

 

 

0 Likes
Reply
Ksharp
Super User Ksharp
Super User

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

Posted 2 weeks ago (257 views)  |   In reply to Ronein
Then use the right most variable P30:

compute P30;
if dif_p10>0.05 then call define ('P10',"style", "style={background=red}");
0 Likes
Reply
Ksharp
Super User Ksharp
Super User

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

Posted 2 weeks ago (283 views)  |   In reply to Ronein
"I didnt see any information to read about this rule "
Why not make an example to test this rule ?
0 Likes
Reply
Ronein
Onyx | Level 15 Ronein
Onyx | Level 15

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

Posted 2 weeks ago (263 views)  |   In reply to Ksharp

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

 

0 Likes
Reply
Kathryn_SAS
SAS Employee Kathryn_SAS
SAS Employee

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

Posted 2 weeks ago (234 views)  |   In reply to Ronein

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 

 

 

0 Likes
Reply

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Watch How to Concatenate Values on YouTube

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 2 weeks ago
  • 366 views
  • 0 likes
  • 3 in conversation