Using PROC FORMAT to colour cells in PROC REPORT

[TimCampbell]

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;

[Ksharp]

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

[TimCampbell]

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.

[Ksharp]

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

[TimCampbell]

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_');

[Ksharp]

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 .

[TimCampbell]

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.

[jwillis]

Thank you for the education!  Learning is good and should always be constant!

[jwillis · Accepted solution]

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.

[RW9]

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?

[Cynthia_sas]

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

[jwillis]

Thank you.  I appreciate the wisdom you share.

[Ksharp]

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

[TimCampbell]

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;