BookmarkSubscribeRSS Feed
vimal
SAS Employee
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%
5 REPLIES 5
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
Cynthia_sas
SAS Super FREQ
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
vimal
SAS Employee
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.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
Cynthia_sas
SAS Super FREQ
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 822 views
  • 0 likes
  • 3 in conversation