The SAS Output Delivery System and reporting techniques

How to give conditonal color option.

Reply
SAS Employee
Posts: 17

How to give conditonal color option.

Dear,
Below is the output we are getting in 2 COMPUTED column through PROC REPORT
New Old
A 44% 6%
B 50% 9%
C 40% 8%
D 37% 7%
E 36% 14%
F 40% 9%
G 55% 0%
H 42% 10%
I 25% 15%
J 36% 13%
I want to color top two value in 'New' with Red & Bottom two with Green color. except the value which is 0%. reverse with 'Old' Column.
ex. In New GREEN - 25% ,36% RED - 55%,50%
ex. In Old RED - 6% ,7% GREEN - 15%,14%
Super Contributor
Super Contributor
Posts: 3,174

Re: How to give conditonal color option.

Suggested Google advanced search argument for the SAS support http://support.sas.com/ website where you will find technical conference papers with examples as well as SAS-hosted documentation:

proc report ods style color site:sas.com

Scott Barry
SBBWorks, Inc.
SAS Super FREQ
Posts: 8,868

Re: How to give conditonal color option.

Hi:
There are two ways to perform "trafficlighting" or conditional formatting in PROC REPORT code:
1) User-defined format method
2) CALL DEFINE method

Method #1 is useful when you want the SAME condition to be tested and used for trafficlighting. For example:
[pre]
** User defined format;
proc format;
value salfmt low-5000='white'
5001-7499 = 'yellow'
7500-high = 'green';
run;

** refer to format in PROC REPORT code;
proc report ...;
column region product sales;
define sales / sum 'Sales' style(column)={background=salfmt.};
[/pre]

In this method, you specify the format as the value for the style attribute.

In method 2, you have more flexibility with the CALL DEFINE statement in a COMPUTE block -- you can format an entire report ROW, you can perform trafficlighting based on multiple conditions (as opposed to method 1, where you can only trafficlight based on the variable value). A CALL DEFINE statement might look like this:
[pre]
proc report ...;
column region product sales;
define sales /sum 'Sales';
compute sales;
if sales gt 5000 then do;
call define(_COL_,'style','style={background=yellow}');
end;
else if sales le 5000 and product = 'Boots' then do;
call define(_COL_,'style','style={foreground=red}');
end;
endcomp;
[/pre]

For more information about performing traffic lighting, you can search on STYLE= and PROC REPORT. Here are a few papers that might prove useful, too.
http://www2.sas.com/proceedings/sugi26/p149-26.pdf
http://www.caloxy.com/papers/69-TUT.pdf


cynthia
SAS Employee
Posts: 17

Re: How to give conditonal color option.

Posted in reply to Cynthia_sas
Dear ,
we cant't specify the values for color as these value will change on daily basis, we want top 2 ranker be in Green and last 2 ranker be Red.
Please explain, if we can do the same or not.
Super Contributor
Super Contributor
Posts: 3,174

Re: How to give conditonal color option.

Given your color attribute varies based on your data values, use PROC SORT with DESCENDING to order the data-values, then use a DATA step to determine/identify the high/low/other attributes, and lastly use PROC FORMAT with the CNTLIN= parameter and using your input data, generate the desired SAS format with the START values (from the input data) and the desired LABEL attributes (color).

Google advanced search argument to find info about generating PROC FORMAT with CNTLIN= as you format input:


proc format cntlin site:sas.com

Scott Barry
SBBWorks, Inc.
SAS Super FREQ
Posts: 8,868

Re: How to give conditonal color option.

Hi:
Yes, you can do that kind of color coding. However, as Scott explained, you'd have to programmatically set the trafficlighting based on the ranking. This is the kind of task that is easy to do within a SAS macro program or just using SAS macro variables that were created before the PROC REPORT step. For example, look at step 8 in this paper:
http://www2.sas.com/proceedings/sugi28/056-28.pdf
where the mean value is being determined with PROC SQL and put into a macro variable. Then the macro variable is used in a PROC REPORT. You may or may not need a macro program; however, you will have to look outside PROC REPORT to figure out the 2 highest & 2 lowest each time the report is run.

You would automatically capture the 2 highest and 2 lowest values every time you run your report and then use those values in your PROC REPORT program. In this case, I'd probably create 4 macro variables &HIGH1 and &HIGH2 for the 2 highest values and then &LOW1 and &LOW2 for the 2 lowest values. Then you could bypass the PROC FORMAT approach and directly code your COMPUTE block. This might be more straightforward than the PROC FORMAT approach, because you only have to create the macro variables and then use them in the CALL DEFINE logic, like this (in my example, HIGH = green and LOW= red):
[pre]
*** some procedure/program to create macro variables &HIGH1, &HIGH2, etc;

proc report ...;
column region product sales;
define sales /sum 'Sales';
compute sales;
if sales eq &high1 or sales eq &high2 then do;
call define(_COL_,'style','style={background=green}');
end;
else if sales eq &low1 or sales eq &low2 then do;
call define(_COL_,'style','style={background=red}');
end;
endcomp;
[/pre]

If (as your original post suggests) you have different criteria/coloring for different variables, you'd have to create multiple macro variables. For example if your variables are named NEW and OLD, you might need to make 4 macro variables for each variable:
&N_HI1, &N_HI2, &N_LO1, &N_LO2 (high and low for NEW var)
&O_HI1, &O_HI2, &O_LO1, &O_LO2 (high and low for OLD var)
and then you'd need a COMPUTE block for the NEW var with a CALL DEFINE and a COMPUTE block for the OLD var with a CALL DEFINE.

cynthia
Ask a Question
Discussion stats
  • 5 replies
  • 152 views
  • 0 likes
  • 3 in conversation