I searched this group on the topic and tried applying the solutions to my table, but it doesn't work.
I have a dummy dataset (temp1) with 4 columns, label, value1, value2, value3 (note that value3 is dummy data and will be different than value2 in the actual data)
I want to color code column Value1 red if it's less than Value2 and green if it's more than Value2.
Label | Value1 | Value2 | Value3 |
Sales | 2725865.52 | 4167644.47 | 4167644.47 |
Profit | 3.987452276 | 6.563600013 | 6.563600013 |
Income | -8.012547724 | -5.436399987 | -5.436399987 |
% Profit | 0.054198967 | 0.091224184 | 0.091224184 |
% Expense | 0.16310856 | 0.166781981 | 0.166781981 |
Average Sales | 73.57063291 | 71.95021874 | 71.95021874 |
I tried the following Proc Report but the entire Column Value 1 turns green. I am not sure why that's happening. Any solutions?
proc report nowd data=temp1
STYLE(header) = HEADER{background=lightBlue fontsize=2}
STYLE(Report) = {frame=box bordercolor = grey borderwidth=2px};
column Label value1 value2 value3;
define LABEL/ " " missing;
define value1 / display 'Value 1' STYLE(COLUMN)=[JUST=l] format=DOLLAR20. missing;
define value2 /display 'Value 2' STYLE(COLUMN)=[JUST=l] format=DOLLAR20. missing;
define value3 /display 'Value 3' STYLE(COLUMN)=[JUST=l] format=DOLLAR20. missing;
compute Label;
call define (_col_, "style", "STYLE=[BACKGROUND=MISTYROSE]");
endcomp;
compute value1;
if (label = "% Profit") then do;
call define (_col_, 'format', 'percent9.1');
end;
if (label = "% Expense") then do;
call define (_col_, 'format', 'percent9.1');
end;
if value1 >= value2 THEN CALL DEFINE(_col_, "STYLE", "STYLE={BACKGROUND=green}");
if value1 < value2 THEN CALL DEFINE(_col_, "STYLE", "STYLE={BACKGROUND=red}");
endcomp;
compute value2;
if (label = "% Profit") then do;
call define (_col_, 'format', 'percent9.1');
end;
if (label = "% Expense") then do;
call define (_col_, 'format', 'percent9.1');
end;
call define (_col_, "style", "STYLE=[BACKGROUND=LIGHTYELLOW]");
endcomp;
compute value3;
if (label = "% Profit") then do;
call define (_col_, 'format', 'percent9.1');
end;
if (label = "% Expense") then do;
call define (_col_, 'format', 'percent9.1');
end;
call define (_col_, "style", "STYLE=[BACKGROUND=LIGHTGREY]");
endcomp;
RUN;
quit;
compute value1;
if (label = "% Profit") then do;
call define (_col_, 'format', 'percent9.1');
end;
if (label = "% Expense") then do;
call define (_col_, 'format', 'percent9.1');
end;
if value1 >= value2 THEN CALL DEFINE(_col_, "STYLE", "STYLE={BACKGROUND=green}");
if value1 < value2 THEN CALL DEFINE(_col_, "STYLE", "STYLE={BACKGROUND=red}");
endcomp;
Code should always be presented in a code box (use the "little running man" icon) and properly indented.
The error happens because you cannot refer to value2 in the COMPUTE block for value1. The way SAS designed PROC REPORT, you can only refer to columns to the left of the current column, and value2 is to the right of the current column.
A possible solution:
proc report nowd data=temp1
STYLE(header) = HEADER{background=lightBlue fontsize=2}
STYLE(Report) = {frame=box bordercolor = grey borderwidth=2px};
column Label value2 value1 value2a=value2 value3;
define LABEL/ " " missing;
define value2 / noprint display;
define value1 / display 'Value 1' STYLE(COLUMN)=[JUST=l] format=DOLLAR20. missing;
define value2a /display 'Value 2' STYLE(COLUMN)=[JUST=l] format=DOLLAR20. missing;
define value3 /display 'Value 3' STYLE(COLUMN)=[JUST=l] format=DOLLAR20. missing;
...
This should now allow you to use value2 in a formula in the compute block for value1 (because now value2 is to the left of value1), and value2a as the next column. This is untested, as I don't have your data. Data should be provided as working SAS data step code (examples and instructions).
compute value1;
if (label = "% Profit") then do;
call define (_col_, 'format', 'percent9.1');
end;
if (label = "% Expense") then do;
call define (_col_, 'format', 'percent9.1');
end;
if value1 >= value2 THEN CALL DEFINE(_col_, "STYLE", "STYLE={BACKGROUND=green}");
if value1 < value2 THEN CALL DEFINE(_col_, "STYLE", "STYLE={BACKGROUND=red}");
endcomp;
Code should always be presented in a code box (use the "little running man" icon) and properly indented.
The error happens because you cannot refer to value2 in the COMPUTE block for value1. The way SAS designed PROC REPORT, you can only refer to columns to the left of the current column, and value2 is to the right of the current column.
A possible solution:
proc report nowd data=temp1
STYLE(header) = HEADER{background=lightBlue fontsize=2}
STYLE(Report) = {frame=box bordercolor = grey borderwidth=2px};
column Label value2 value1 value2a=value2 value3;
define LABEL/ " " missing;
define value2 / noprint display;
define value1 / display 'Value 1' STYLE(COLUMN)=[JUST=l] format=DOLLAR20. missing;
define value2a /display 'Value 2' STYLE(COLUMN)=[JUST=l] format=DOLLAR20. missing;
define value3 /display 'Value 3' STYLE(COLUMN)=[JUST=l] format=DOLLAR20. missing;
...
This should now allow you to use value2 in a formula in the compute block for value1 (because now value2 is to the left of value1), and value2a as the next column. This is untested, as I don't have your data. Data should be provided as working SAS data step code (examples and instructions).
possible option would be creating flag variables and "style/replace"
data temp2;
infile cards4 dlm="|";
input Label : $ 20. Value1 Value2 Value3;
flag1=value1 >= value2;
flag2=not flag1;
cards4;
Sales|2725865.52|4167644.47|4167644.47
Profit|3.987452276|6.563600013|6.563600013
Income|-8.012547724|-5.436399987|-5.436399987
% Profit|0.154198967|0.091224184|0.091224184
% Expense|0.16310856|0.166781981|0.166781981
Average Sales|73.57063291|71.95021874|71.95021874
;;;;
run;
proc report nowd data=temp2
STYLE(header) = HEADER{background=lightBlue fontsize=2}
STYLE(Report) = {frame=box bordercolor = grey borderwidth=2px};
column Label value1 value2 value3 flag1 flag2;
define LABEL/ " " missing;
define value1 / display 'Value 1' STYLE(COLUMN)=[JUST=l] format=DOLLAR20. missing;
define value2 / display 'Value 2' STYLE(COLUMN)=[JUST=l] format=DOLLAR20. missing;
define value3 / display 'Value 3' STYLE(COLUMN)=[JUST=l] format=DOLLAR20. missing;
define flag1 / NOPRINT display missing;
define flag2 / NOPRINT display missing;
compute Label;
call define (_col_, "style", "STYLE=[BACKGROUND=MISTYROSE]");
endcomp;
compute Value1;
if (label = "% Profit") then do;
call define (_col_, 'format', 'percent9.1');
end;
if (label = "% Expense") then do;
call define (_col_, 'format', 'percent9.1');
end;
endcomp;
compute value2;
if (label = "% Profit") then do;
call define (_col_, 'format', 'percent9.1');
end;
if (label = "% Expense") then do;
call define (_col_, 'format', 'percent9.1');
end;
call define (_col_, "style", "STYLE=[BACKGROUND=LIGHTYELLOW]");
endcomp;
compute value3;
if (label = "% Profit") then do;
call define (_col_, 'format', 'percent9.1');
end;
if (label = "% Expense") then do;
call define (_col_, 'format', 'percent9.1');
end;
call define (_col_, "style", "STYLE=[BACKGROUND=LIGHTGREY]");
endcomp;
compute flag1;
if (flag1 = 1) then
call define('value1', "style", "style=[background=green]");
else call define('value1', "style", "style=[background=red]");
endcomp;
compute flag2;
if (flag2 = 1) then
call define('value1', "style/replace", "style=[background=red]");
else call define('value1', "style/replace", "style=[background=green]");
endcomp;
RUN;
quit;
it produces:
based on:
https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/proc/p0934x0xj13fm9n1l164y36xttk1.htm
Bart
Both solutions worked!! Thank you.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.