Hi All,
I have a dataset with actual values and % values for each month as below
SEG
AMOUNT
M1
M2
M3
M1%
M2%
M3%
ABC
100
2,200
2,420
2,662
97%
98%
100%
ABC
200
1,458
1,604
1,764
93%
95%
99%
ABC
300
1,345
1,480
1,627
96%
100%
99%
ABC
400
870
957
1,053
87%
97%
96%
ABC
500
554
609
670
68%
92%
99%
BCD
100
4,450
4,895
5,385
99%
98%
97%
BCD
200
1,278
1,406
1,546
93%
96%
100%
BCD
300
3,345
3,680
4,047
100%
88%
94%
BCD
400
1,879
2,067
2,274
99%
97%
97%
BCD
500
665
732
805
78%
91%
99%
I need to color code both the columns M1 & M1% cells based on M1% column values. Let say if M1%>95% then green (both M1 & M1% cells); else if m1% >90% then orange; else red. The same format should apply for all the subsequent columns as below.
I did try using the PROC REPORT to achieve the result but unable to get color format for both actuals and percentage values at the same time in proc report. Below is snippet used to achieved the partial results.
Query:
proc report data=work.freq_accts;
column cas level M1 M2 M1% M1% M2% M1%%;
define cas / display 'CAS';
define level / display 'Plan Amt';
define M1 / display '1Month' ;
define M2 / display '2Months' ;
define M3% / display '3Months' ;
define M1% / display 'M1%' ;
define M2% / display 'M2%' ;
define M3% / display 'M3%' ;
compute M1%;
if M1% > .95 then
call define ('M1', "style","style={background=light green}");
else if M1% > .90 then
call define ('M1', "style","style={background=light orange}");
else if M1% ne . then
call define ('M1', "style","style={background=red}");
endcomp;
compute M2%;
if M2% > .95 then
call define ('M2', "style","style={background=light green}");
else if M2% > .90 then
call define ('M2', "style","style={background=light orange}");
else if M2% ne . then
call define ('M2', "style","style={background=red}");
endcomp;
compute M3%;
if M3% > .95 then
call define ('M1', "style","style={background=light green}");
else if M3% > .90 then
call define ('M1', "style","style={background=light orange}");
else if M3% ne . then
call define ('M1', "style","style={background=red}");
endcomp;
run;
Based on the above query able to get the results as below
Now I wanted to color code the other columns as well based on the same condition. If not possible need to eliminate the % columns and keep the color format for actuals.
Thanks in advance.
Thanks,
Neel
... View more
