Conditional formatting: Colors/Colours

[rjacko10]

Hi all,

I'm trying to generate some conditional formatting in my proc tabulate table that will resemble the conditional formatting in MS Excel.

I want all the rows in my table to share the same formatting rule, however each row must only consider its own data.

e.g.

Row 1          1   2   3   2   1   2   3

Row 2          4   5   6   5   4   5   6

Row 3          7   8   9   8   7   8   9

In the example above I would want the lowest number of each row to be in RED, the middle number to be in ORANGE, and the highest number to be in YELLOW.

Therefore my proc format statement will not be based on values, but on percentiles of the data found in each row

i.e.

proc format;

     value color          0-33%      = red

                              34-66%     = orange

                              67-100%   = yellow

Is this possible? If so how can I code this? And how do get SAS to treat each row's data independently?

Thank you very much for your help. I'm new to SAS and finding it quite difficult!

[Reeza]

I don't think there's a simple solution to your problem, you probably have to precalculate the fields you want highlighted and then highlight them.

Out of curiosity how would you go about this in Excel as well?

[rjacko10]

Thanks Reeza. Do you know of a way to use separate values for each row of output? Something like below....

i.e.

[IF ROW=1 then..]

proc format;

     value color1         1      = red

                               2      = orange

                               3      = yellow

[IF ROW=2 then..]

proc format;

     value color2         4      = red

                               5      = orange

                               6      = yellow

For Excel I just highlight each row individually and select the conditional formatting colour scheme. I have to do this for each row, hence it can take a while depending on the number of rows...

[Cynthia_sas · Accepted solution]

Hi,

  You can do row level highlighting based on a column value in PROC REPORT. You would use a CALL DEFINE statement in a COMPUTE block. You might be able to use a user-deifned format, but you can't highlight based on percentage "on the fly" -- you would probably have to pre-calculate the value before the PROC REPORT step and/or calculate them in a COMPUTE block.

  In my SAS Global Forum paper, I show an example of row level highlighting with PROC REPORT.

http://support.sas.com/resources/papers/proceedings13/366-2013.pdf

  There's also an example of row level "banding" with PROC TABULATE, but the variable that sets the color for the band is a CLASS variable, not a numeric variable, which I why I think you'll have to go with PROC REPORT.

cynthia

[Ksharp]

As Cynthia point out, Traffic Light is a good choice .