Hi, I am trying to produce a report where different cells get a traffic light colour based on values within the table. The issue I am having is that I can use proc format to define how the colour coding should happen but only if I use named colours rather than hex colours. I have tested my call define statements using direct references to the hex colours and get the result I want but the code is awful by comparison to the one where I use the format. The code I am using is below, can anyone see what is going wrong with this? Thanks in advance, Tim data _test_data; across_var='Value1'; do x=0.001 to 0.501 by 0.05; n+1; group_var='Group'||n; y='test1'; z='test2'; output; end; n=0; across_var='Value2'; do x=0.301 to 0.701 by 0.05; n+1; group_var='Group'||n; y='test3'; z='test4'; output; end; run; proc format; picture hextraffic low-<0='style=[background=white]' 0-<0.1='style=[background=cxF7977A]' 0.1-<0.2='style=[background=cxF9AD81]' 0.2-<0.3='style=[background=cxFDC68A]' 0.3-<0.4='style=[background=cxFFF79A]' 0.4-<0.5='style=[background=cxC4DF9B]' 0.5-<0.6='style=[background=cxA2D39C]' 0.6-high='style=[background=cx82CA9D]' other='style=[background=white]' ; picture wordtraffic low-<0='style=[background=white]' 0-<0.1='style=[background=red]' 0.1-<0.2='style=[background=red]' 0.2-<0.3='style=[background=yellow]' 0.3-<0.4='style=[background=yellow]' 0.4-<0.5='style=[background=green]' 0.5-<0.6='style=[background=green]' 0.6-high='style=[background=blue]' other='style=[background=white]' ; run; title 'format method that works but named colours only'; proc report data=_test_data; columns group_var across_var,(y z x); define group_var / group 'Group' 'Variable'; define across_var / across ''; define y / 'other' 'col1'; define z / 'other' 'col2'; define x / 'colour' 'key'; compute x; call define('_c2_','style',put(_c4_,wordtraffic.)); call define('_c3_','style',put(_c4_,wordtraffic.)); call define('_c4_','style',put(_c4_,wordtraffic.)); call define('_c5_','style',put(_c7_,wordtraffic.)); call define('_c6_','style',put(_c7_,wordtraffic.)); call define('_c7_','style',put(_c7_,wordtraffic.)); endcomp; run; title 'format using hex colours that does not work'; proc report data=_test_data; columns group_var across_var,(y z x); define group_var / group 'Group' 'Variable'; define across_var / across ''; define y / 'other' 'col1'; define z / 'other' 'col2'; define x / 'colour' 'key'; compute x; call define('_c2_','style',put(_c4_,hextraffic.)); call define('_c3_','style',put(_c4_,hextraffic.)); call define('_c4_','style',put(_c4_,hextraffic.)); call define('_c5_','style',put(_c7_,hextraffic.)); call define('_c6_','style',put(_c7_,hextraffic.)); call define('_c7_','style',put(_c7_,hextraffic.)); endcomp; run; title 'Method without using format but right result from hex colours'; proc report data=_test_data; columns group_var across_var,(y z x); define group_var / group 'Group' 'Variable'; define across_var / across ''; define y / 'other' 'col1'; define z / 'other' 'col2'; define x / 'colour' 'key'; compute x; if _c4_=. then do; call define('_c2_','style','style=[background=white]'); call define('_c3_','style','style=[background=white]'); call define('_c4_','style','style=[background=white]'); end; else if _c4_<0.1 then do; call define('_c2_','style','style=[background=cxF7977A]'); call define('_c3_','style','style=[background=cxF7977A]'); call define('_c4_','style','style=[background=cxF7977A]'); end; else if _c4_<0.2 then do; call define('_c2_','style','style=[background=cxF9AD81]'); call define('_c3_','style','style=[background=cxF9AD81]'); call define('_c4_','style','style=[background=cxF9AD81]'); end; else if _c4_<0.3 then do; call define('_c2_','style','style=[background=cxFDC68A]'); call define('_c3_','style','style=[background=cxFDC68A]'); call define('_c4_','style','style=[background=cxFDC68A]'); end; else if _c4_<0.4 then do; call define('_c2_','style','style=[background=cxFFF79A]'); call define('_c3_','style','style=[background=cxFFF79A]'); call define('_c4_','style','style=[background=cxFFF79A]'); end; else if _c4_<0.5 then do; call define('_c2_','style','style=[background=cxC4DF9B]'); call define('_c3_','style','style=[background=cxC4DF9B]'); call define('_c4_','style','style=[background=cxC4DF9B]'); end; else if _c4_<0.6 then do; call define('_c2_','style','style=[background=cxA2D39C]'); call define('_c3_','style','style=[background=cxA2D39C]'); call define('_c4_','style','style=[background=cxA2D39C]'); end; else do; call define('_c2_','style','style=[background=cx82CA9D]'); call define('_c3_','style','style=[background=cx82CA9D]'); call define('_c4_','style','style=[background=cx82CA9D]'); end; if _c7_=. then do; call define('_c5_','style','style=[background=white]'); call define('_c6_','style','style=[background=white]'); call define('_c7_','style','style=[background=white]'); end; else if _c7_<0.1 then do; call define('_c5_','style','style=[background=cxF7977A]'); call define('_c6_','style','style=[background=cxF7977A]'); call define('_c7_','style','style=[background=cxF7977A]'); end; else if _c7_<0.2 then do; call define('_c5_','style','style=[background=cxF9AD81]'); call define('_c6_','style','style=[background=cxF9AD81]'); call define('_c7_','style','style=[background=cxF9AD81]'); end; else if _c7_<0.3 then do; call define('_c5_','style','style=[background=cxFDC68A]'); call define('_c6_','style','style=[background=cxFDC68A]'); call define('_c7_','style','style=[background=cxFDC68A]'); end; else if _c7_<0.4 then do; call define('_c5_','style','style=[background=cxFFF79A]'); call define('_c6_','style','style=[background=cxFFF79A]'); call define('_c7_','style','style=[background=cxFFF79A]'); end; else if _c7_<0.5 then do; call define('_c5_','style','style=[background=cxC4DF9B]'); call define('_c6_','style','style=[background=cxC4DF9B]'); call define('_c7_','style','style=[background=cxC4DF9B]'); end; else if _c7_<0.6 then do; call define('_c5_','style','style=[background=cxA2D39C]'); call define('_c6_','style','style=[background=cxA2D39C]'); call define('_c7_','style','style=[background=cxA2D39C]'); end; else do; call define('_c5_','style','style=[background=cx82CA9D]'); call define('_c6_','style','style=[background=cx82CA9D]'); call define('_c7_','style','style=[background=cx82CA9D]'); end; endcomp; run; title;
... View more