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!
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
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?
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...
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
As Cynthia point out, Traffic Light is a good choice .
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.