topic Color coding columns in proc report in SAS Programming

Color coding columns in proc report

monali — Thu, 04 May 2023 19:06:28 GMT

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;

Re: Color coding columns in proc report

PaigeMiller — Thu, 04 May 2023 19:26:10 GMT

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

Re: Color coding columns in proc report

yabwon — Thu, 04 May 2023 20:17:24 GMT

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

Re: Color coding columns in proc report

monali — Thu, 04 May 2023 20:32:35 GMT

Both solutions worked!! Thank you.