topic Re: Problem Traffic Lighting with Proc Report in ODS and Base Reporting

Problem Traffic Lighting with Proc Report

saiaca — Tue, 05 Aug 2014 16:20:15 GMT

Hi,

I am trying to create a set of reports that needs to be color coded (background) based on a range of values that I have already grouped. I have successfully colored the rows the correct colors based on the values but I really need to highlight only two of the columns, not all of them. I have tried many different ways to color indiviudal columns, but it only worked with all rows. Here is my code for one of the reports (that works to color the rows):

proc report data= comp nowd spansrows style= {background= white foreground=black };

column title unit class count month percent;

define title/ order=data ;

define unit/ order=data ;

define class/ order=data ;

define count/ order=data ;

define month/ order=data ;

define percent/ order=internal ;

/* hidden column containing color flag*/

column category;

/* dummy column for traffic lighting*/

column dummy;

define category/ display noprint;

/* traffic light the data columns based on category*/

define dummy/ computed noprint;

compute dummy;

if category in (1,2,3,4) then call define (_row_), 'style', 'style= {background=' || put (category, colorflag.)']');

endcomp;

run;

The category variable is 1-4 based off of the percent values.

and the format 'colorflag.' is (1=red, 2=orange, 3= yellow, 4=green).

As previously stated, this code works for coloring all rows but I only want title and percent to be colored based off of the category. Changing "_row_" has been unsuccessful, so if anyone has any ideas, I'd really appreciate it!

- Chelsea

Re: Problem Traffic Lighting with Proc Report

Cynthia_sas — Wed, 06 Aug 2014 03:07:10 GMT

Hi:

Please carefully review your CALL DEFINE syntax. The parentheses that you show in your code above are incorrect. You should be getting errors in the log. The SPANROWS option is misspelled as SPANSROWS, which would generate an error. But the CALL DEFINE problem is a bigger problem. I like to avoid making my string in the CALL DEFINE statement by using a TEMP variable as shown in the code below. Instead of using _ROW_, I used the numeric variables COUNT and MONTH and the CHARACTER variable CLASS. Note how the reference for the column names is different depending on the USAGE specified (or not specified) in the DEFINE statement. Since it was not clear what your types were, I just made some fake data from SASHELP.CLASS. I picked different colors, too. I thought they were easier on the eyes.

Cynthia

data comp (keep=title unit class count month category);
set sashelp.class;
title=name;
unit=age;
class=sex;
count=height;
month=weight;
if age in (11,12) then category=1;
else if age in (13,14) then category=2;
else if age in (15) then category=3;
else category=4;
run;

proc format;
value colorflag 1='lightblue'
                  2='lightyellow'
      3='lightgreen'
      4='pink';
run;

** It is better to only have 1 column statement so I took out the multiple column statements;

title; footnote;

ods html file='c:\temp\colorcode.html';

proc report data= comp nowd spanrows style= {background= white foreground=black };
column title unit class count month category dummy;
** using ORDER=DATA does not have any impact without a;
** usage of ORDER or GROUP on the report item -- so I took it off;
    ** remember that default usage for character vars is DISPLAY;
    ** and default usage for numeric vars is ANALYSIS SUM;
    ** so the CALL DEFINE "reference" for COUNT is COUNT.SUM and for
    ** MONTH is MONTH.SUM;
     define title/ 'title';
     define unit/ "unit";
     define class/ 'class' ;
     define count/ 'count' ;
     define month/ 'month' ;

     /* hidden column containing color flag*/
/* will use NOPRINT later */
     define category/ display ;
     define dummy / computed;

/* dummy column for traffic lighting*/
/* traffic light the data columns based on category*/
compute dummy/character length=100;
** make a temp variable big enough to hold whole style string;
length svar $100;
if category in (1,2,3,4) then do;
    svar = catt('style={background=', put (category, colorflag.),'}');
    dummy = svar;
** need different column reference for numeric vs character;
** if COUNT and MONTH had been a usage or display, then it would have been OK;
** to use the simple name. ;

    call define ('count.sum' , 'style', svar);
    call define ('month.sum', 'style', svar);
    call define ('class','style',svar);
end;
endcomp;
run;

ods html close;

Re: Problem Traffic Lighting with Proc Report

saiaca — Wed, 06 Aug 2014 12:09:59 GMT

Thank you so much Cynthia! The report works and looks great, I really appreciate your quick and detailed response! (The parts of the code that were misspelled/ wrong parenthesis were due to my having to type out the code by hand (SAS is on a different computer without internet). Thanks again!