Help using Base SAS procedures

Using PROC FORMAT to colour cells in PROC REPORT

Accepted Solution Solved
Reply
Contributor
Posts: 45
Accepted Solution

Using PROC FORMAT to colour cells in PROC REPORT

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;


Accepted Solutions
Solution
‎07-28-2014 09:54 AM
Regular Contributor
Posts: 217

Re: Using PROC FORMAT to colour cells in PROC REPORT

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.

View solution in original post


All Replies
Super User
Posts: 9,779

Re: Using PROC FORMAT to colour cells in PROC REPORT

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

Contributor
Posts: 45

Re: Using PROC FORMAT to colour cells in PROC REPORT

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.

Super User
Posts: 9,779

Re: Using PROC FORMAT to colour cells in PROC REPORT

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

Contributor
Posts: 45

Re: Using PROC FORMAT to colour cells in PROC REPORT

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

Super User
Posts: 9,779

Re: Using PROC FORMAT to colour cells in PROC REPORT

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 .

Contributor
Posts: 45

Re: Using PROC FORMAT to colour cells in PROC REPORT

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.

Regular Contributor
Posts: 217

Re: Using PROC FORMAT to colour cells in PROC REPORT

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

Solution
‎07-28-2014 09:54 AM
Regular Contributor
Posts: 217

Re: Using PROC FORMAT to colour cells in PROC REPORT

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.

Super User
Super User
Posts: 7,565

Re: Using PROC FORMAT to colour cells in PROC REPORT

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?

SAS Super FREQ
Posts: 8,781

Re: Using PROC FORMAT to colour cells in PROC REPORT

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

Regular Contributor
Posts: 217

Re: Using PROC FORMAT to colour cells in PROC REPORT

Thank you.  I appreciate the wisdom you share.

Super User
Posts: 9,779

Re: Using PROC FORMAT to colour cells in PROC REPORT

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

Contributor
Posts: 45

Re: Using PROC FORMAT to colour cells in PROC REPORT

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 868 views
  • 4 likes
  • 5 in conversation