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;
Tim,
I'm intrigued by your code. First, you are using _c6_ and _c7_ however I only count 5 columns in your columns statement: "group_var across_var,(y z x);". For my education, will you explain what _c6_ and _c7_ represent? Second, I created code to do similar output as yours however I used a value statement in the proc format not the picture statement. I had no problem with the value statement. Lastly, I output my report to ODS PDF and had no problems. I read somewhere that Hex codes are not 'valid' in some output destinations. I'll try to find out where I read that.
You only can apply this format to _c4_ and _c7_ , because your format is a numeric type .
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-Xia Keshan
That isn't correct. If you try running the whole code you will see that the first report example I gave uses the value of _c4_ to colour _c2_ and _c3_ correctly when using the word colours rather than hex. sorry if i wasn't clear in the original post, my aim is to get a report like the one generated as report #3 but using code closer to report #2.
That is weird . I checked your table with out= . and only _c4_ and _c7_ is numeric .
You can't use traffic light in that way . I am surprised that you can make report #2 .
Another way is overriding the value of other variables.
You can refer to my code as your need . my code had been trimmed. I should posted it again.
If you want apply the traffic light into character variable ,you can also make a character type format .
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='white'
0-<0.1='cxF7977A'
0.1-<0.2='cxF9AD81'
0.2-<0.3='cxFDC68A'
0.3-<0.4='cxFFF79A'
0.4-<0.5='cxC4DF9B'
0.5-<0.6='cxA2D39C'
0.6-high='cx82CA9D'
other='white'
;
run;
ods html file='x.html' style=sasweb;
proc report data=_test_data nowd;
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('_c4_','style','style={background=hextraffic.}');
call define('_c7_','style','style={background=hextraffic.}');
endcomp;
run;
ods html close;
Xia Keshan
Message was edited by: xia keshan
it is true that only _c4_ and _c7_ are numeric but report #1 uses the value from the numeric columns to colour the character columns so i know it is possible but still not sure why report #2 isn't working. is there another way i could get the same result? something like call define('_c2_','style','style=_c4_');
Why not use the last report code you wrote ? You can do it as well . The only thing I can think is overriding the value like something :
if _c4_ < 0.1 then _c2_=catx(' ','~S={ background=hex.}' , _c2_ ) ;
and add ods escapechar='~' to make it real .
Cosmetics more than anything else.
26 lines of code is much better than 108 if other people will need to suppot the report in the future.
Also if i need to change the colour band sizes or colours i only need to edit the proc format and my proc report will just work rather than having a 108 line proc report step to update.
Thank you for the education! Learning is good and should always be constant!
Tim,
I'm intrigued by your code. First, you are using _c6_ and _c7_ however I only count 5 columns in your columns statement: "group_var across_var,(y z x);". For my education, will you explain what _c6_ and _c7_ represent? Second, I created code to do similar output as yours however I used a value statement in the proc format not the picture statement. I had no problem with the value statement. Lastly, I output my report to ODS PDF and had no problems. I read somewhere that Hex codes are not 'valid' in some output destinations. I'll try to find out where I read that.
Mmm, was just coming to the same conclusion there, change picture to value. No idea why it wouldn't work, maybe a storage issue in that picture formats do not have the required quotes or something?
Hi, well, I'm late to the party. A PICTURE format would not work for this situation. A PICTURE format sets a "mask" or "pattern" for a number to be displayed. For example:
1234567890 could be "masked" as: 123-456-7890 (phone) or
1234567890 "masked" as (123) 456-7890 or
123456789 123-45-6789 (SSN)
For a picture, the "mask" is essentially saying how the numbers in the format should be displayed or manipulated.
But a VALUE format is like totally changing the display so that a value of .15 (in the example above) would be "translated to" or used to generate this string: 'style=[background=cxF9AD81]' -- so for what the OP wanted, a PICTURE was the wrong type of FORMAT.
cynthia
Thank you. I appreciate the wisdom you share.
Cynthia , That is really strange . Did you see my code above .It still worked with PICTURE . That is what I am confused. I should consider PICTURE would be a problem firstly, but I overlooked . SAS has too many things we need to dig in .
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='white'
0-<0.1='cxF7977A'
0.1-<0.2='cxF9AD81'
0.2-<0.3='cxFDC68A'
0.3-<0.4='cxFFF79A'
0.4-<0.5='cxC4DF9B'
0.5-<0.6='cxA2D39C'
0.6-high='cx82CA9D'
other='white'
;
run;
ods html file='x.html' style=sasweb;
proc report data=_test_data nowd;
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('_c4_','style','style={background=hextraffic.}');
call define('_c7_','style','style={background=hextraffic.}');
endcomp;
run;
ods html close;
Xia Keshan
Message was edited by: xia keshan
Hi, thanks for that, changing picture to value makes it all work fine.
In answer to your first question, the column across_varis defined as an across variable so the actual columns of the resulting table will be similar to this
group_var (y z x) (y z x)
where i only have 2 distinct values for across_var, for 3 distinct values of across_var i would have 10 columns total
group_var (y z x) (y z x) (y z x)
For anyone’s future reference here is a version of the code that works.
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;
value 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]'
;
run;
title 'Correct Output';
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: