topic Re: Using PROC FORMAT to colour cells in PROC REPORT in SAS Procedures

Using PROC FORMAT to colour cells in PROC REPORT

TimCampbell — Thu, 24 Jul 2014 11:05:04 GMT

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;

Re: Using PROC FORMAT to colour cells in PROC REPORT

Ksharp — Thu, 24 Jul 2014 12:43:39 GMT

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

Re: Using PROC FORMAT to colour cells in PROC REPORT

TimCampbell — Thu, 24 Jul 2014 13:28:53 GMT

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.

Re: Using PROC FORMAT to colour cells in PROC REPORT

Ksharp — Thu, 24 Jul 2014 13:59:23 GMT

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

Re: Using PROC FORMAT to colour cells in PROC REPORT

TimCampbell — Mon, 28 Jul 2014 13:38:01 GMT

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

Re: Using PROC FORMAT to colour cells in PROC REPORT

jwillis — Mon, 28 Jul 2014 13:54:47 GMT

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.

Re: Using PROC FORMAT to colour cells in PROC REPORT

RW9 — Mon, 28 Jul 2014 14:07:15 GMT

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?

Re: Using PROC FORMAT to colour cells in PROC REPORT

TimCampbell — Mon, 28 Jul 2014 14:25:28 GMT

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;

Re: Using PROC FORMAT to colour cells in PROC REPORT

Ksharp — Mon, 28 Jul 2014 14:27:55 GMT

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 .

Re: Using PROC FORMAT to colour cells in PROC REPORT

TimCampbell — Mon, 28 Jul 2014 14:43:55 GMT

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.

Re: Using PROC FORMAT to colour cells in PROC REPORT

jwillis — Mon, 28 Jul 2014 14:54:07 GMT

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

Re: Using PROC FORMAT to colour cells in PROC REPORT

Cynthia_sas — Mon, 28 Jul 2014 20:03:30 GMT

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

Re: Using PROC FORMAT to colour cells in PROC REPORT

jwillis — Mon, 28 Jul 2014 20:10:00 GMT

Thank you. I appreciate the wisdom you share.

Re: Using PROC FORMAT to colour cells in PROC REPORT

Ksharp — Tue, 29 Jul 2014 13:28:28 GMT

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