Help using Base SAS procedures

Conditional formatting: Colors/Colours

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Conditional formatting: Colors/Colours

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!


Accepted Solutions
Solution
‎05-09-2013 12:11 AM
SAS Super FREQ
Posts: 8,864

Re: Conditional formatting: Colors/Colours

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

View solution in original post


All Replies
Super User
Posts: 19,789

Re: Conditional formatting: Colors/Colours

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?

Occasional Contributor
Posts: 7

Re: Conditional formatting: Colors/Colours

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

Solution
‎05-09-2013 12:11 AM
SAS Super FREQ
Posts: 8,864

Re: Conditional formatting: Colors/Colours

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

Super User
Posts: 10,023

Re: Conditional formatting: Colors/Colours

Posted in reply to Cynthia_sas

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 4 replies
  • 2891 views
  • 0 likes
  • 4 in conversation