BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
TimCampbell
Quartz | Level 8

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;

1 ACCEPTED SOLUTION

Accepted Solutions
jwillis
Quartz | Level 8

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

13 REPLIES 13
Ksharp
Super User

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
Quartz | Level 8

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
Super User

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
Quartz | Level 8

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
Super User

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
Quartz | Level 8

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
Quartz | Level 8

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

jwillis
Quartz | Level 8

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
Diamond | Level 26 RW9
Diamond | Level 26

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
SAS Super FREQ

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
Quartz | Level 8

Thank you.  I appreciate the wisdom you share.

Ksharp
Super User

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
Quartz | Level 8

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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