topic Re: Conditional formatting: Colors/Colours in SAS Procedures

Conditional formatting: Colors/Colours

rjacko10 — Wed, 08 May 2013 14:34:31 GMT

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!

Re: Conditional formatting: Colors/Colours

Reeza — Wed, 08 May 2013 14:39:50 GMT

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?

Re: Conditional formatting: Colors/Colours

rjacko10 — Wed, 08 May 2013 16:46:43 GMT

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...

Re: Conditional formatting: Colors/Colours

Cynthia_sas — Thu, 09 May 2013 04:11:37 GMT

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

Re: Conditional formatting: Colors/Colours

Ksharp — Thu, 09 May 2013 14:30:40 GMT

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