topic Re: How to assign colors to a column according to another column in PROC REPORT in SAS Programming

How to assign colors to a column according to another column in PROC REPORT

Relax — Mon, 15 Mar 2021 18:45:05 GMT

Hello All,

I have a small sample dataset, which has three variables, ID, Grade and Result. I want to report this dataset using PROC Report and assign the different colors to Grade based on the value of variable Result. Let's say, if Result is Pass then the background color of Grade is green; otherwise, it would be in red. At the same time, I only want to see ID and Grade in the output, so I use NOPRINT for Result. Here is my code:

DATA WORK.Test;
INFILE DATALINES;
INPUT ID Grade Result $;
DATALINES;
1 90 Pass
2 65 Pass
3 57 Fail
;
RUN;

PROC REPORT DATA=WORK.Test
STYLE(REPORT) = {OUTPUTWIDTH=60%}
STYLE(HEADER) = [JUST = CENTER FONT_FACE = calibri FONT_SIZE = 3 FOREGROUND = Black BACKGROUND = Orange PROTECTSPECIALCHARS=OFF];
COLUMN ID Grade Result;
DEFINE ID / DISPLAY STYLE(COLUMN)=[JUST=l FONT_FACE = calibri FONT_SIZE = 3];
DEFINE Grade / DISPLAY STYLE(COLUMN)=[JUST=l FONT_FACE = calibri FONT_SIZE = 3];
DEFINE Result / NOPRINT STYLE(COLUMN)=[JUST=l FONT_FACE = calibri FONT_SIZE = 3];
COMPUTE Grade;
IF Result = 'Pass' THEN CALL DEFINE("_COL_", "STYLE", "STYLE={BACKGROUND=CX00FF00}");
IF Result = 'Fail' THEN CALL DEFINE("_COL_", "STYLE", "STYLE={BACKGROUND=CXFF0000}");
ENDCOMP;
QUIT;

The output I want is

Could someone kindly help me figure out how to do it?

Thanks in advance!

Re: How to assign colors to a column according to another column in PROC REPORT

ballardw — Mon, 15 Mar 2021 19:25:01 GMT

Try this:

PROC REPORT DATA=WORK.Test
STYLE(REPORT) = {OUTPUTWIDTH=60%}
STYLE(HEADER) = [JUST = CENTER FONT_FACE = calibri FONT_SIZE = 3 FOREGROUND = Black BACKGROUND = Orange PROTECTSPECIALCHARS=OFF];
COLUMN ID Result Grade ;
DEFINE ID / DISPLAY STYLE(COLUMN)=[JUST=l FONT_FACE = calibri FONT_SIZE = 3];
DEFINE Grade / DISPLAY STYLE(COLUMN)=[JUST=l FONT_FACE = calibri FONT_SIZE = 3];
DEFINE Result / NOPRINT STYLE(COLUMN)=[JUST=l FONT_FACE = calibri FONT_SIZE = 3];
COMPUTE Grade;
IF Result = 'Pass' THEN CALL DEFINE(_COL_, "STYLE", "STYLE={BACKGROUND=CX00FF00}");
IF Result = 'Fail' THEN CALL DEFINE(_COL_, "STYLE", "STYLE={BACKGROUND=CXFF0000}");
ENDCOMP;
QUIT;

Two things to change:

Column order. Proc Report builds report items from left to right. So the column Grade can't see Result if the Result column is to the right on the Columns statement.

Second, _col_ is a key word. When you place it in quotes then it becomes a value, which doesn't match the requirement for call define statements.

Re: How to assign colors to a column according to another column in PROC REPORT

Relax — Mon, 15 Mar 2021 19:32:45 GMT

Thanks for your help!

Re: How to assign colors to a column according to another column in PROC REPORT

Reeza — Mon, 15 Mar 2021 19:35:10 GMT

This seems to work as well, though @ballardw has identified the root of the issue - that one often catches me as well with CALL DEFINE.

This uses the option to explicitly name the column being shaded rather than it being implicit, which I personally prefer as it makes the code slightly more legible IMO.

PROC REPORT DATA=WORK.Test
STYLE(REPORT) = {OUTPUTWIDTH=60%}
STYLE(HEADER) = [JUST = CENTER FONT_FACE = calibri FONT_SIZE = 3 FOREGROUND = Black BACKGROUND = Orange PROTECTSPECIALCHARS=OFF];
COLUMN ID Grade Result;
DEFINE ID / DISPLAY STYLE(COLUMN)=[JUST=l FONT_FACE = calibri FONT_SIZE = 3];
DEFINE Grade / DISPLAY STYLE(COLUMN)=[JUST=l FONT_FACE = calibri FONT_SIZE = 3];
DEFINE Result / NOPRINT STYLE(COLUMN)=[JUST=l FONT_FACE = calibri FONT_SIZE = 3];
COMPUTE Result;
IF Result ="Pass" THEN  CALL DEFINE("grade", "style", "style=[background=CX00FF00]");
else IF Result="Fail" THEN  CALL DEFINE("grade", "style", "style=[background=CXFF0000]"); 
ENDCOMP;
QUIT;

@Relax wrote:

Hello All,

I have a small sample dataset, which has three variables, ID, Grade and Result. I want to report this dataset using PROC Report and assign the different colors to Grade based on the value of variable Result. Let's say, if Result is Pass then the background color of Grade is green; otherwise, it would be in red. At the same time, I only want to see ID and Grade in the output, so I use NOPRINT for Result. Here is my code:

DATA WORK.Test;
INFILE DATALINES;
INPUT ID Grade Result $;
DATALINES;
1 90 Pass
2 65 Pass
3 57 Fail
;
RUN;

PROC REPORT DATA=WORK.Test
STYLE(REPORT) = {OUTPUTWIDTH=60%}
STYLE(HEADER) = [JUST = CENTER FONT_FACE = calibri FONT_SIZE = 3 FOREGROUND = Black BACKGROUND = Orange PROTECTSPECIALCHARS=OFF];
COLUMN ID Grade Result;
DEFINE ID / DISPLAY STYLE(COLUMN)=[JUST=l FONT_FACE = calibri FONT_SIZE = 3];
DEFINE Grade / DISPLAY STYLE(COLUMN)=[JUST=l FONT_FACE = calibri FONT_SIZE = 3];
DEFINE Result / NOPRINT STYLE(COLUMN)=[JUST=l FONT_FACE = calibri FONT_SIZE = 3];
COMPUTE Grade;
IF Result = 'Pass' THEN CALL DEFINE("_COL_", "STYLE", "STYLE={BACKGROUND=CX00FF00}");
IF Result = 'Fail' THEN CALL DEFINE("_COL_", "STYLE", "STYLE={BACKGROUND=CXFF0000}");
ENDCOMP;
QUIT;

The output I want is

Could someone kindly help me figure out how to do it?

Thanks in advance!

Re: How to assign colors to a column according to another column in PROC REPORT

Relax — Mon, 15 Mar 2021 20:13:12 GMT

Dig it. Thanks for showing me one more option!

Re: How to assign colors to a column according to another column in PROC REPORT

Reeza — Mon, 15 Mar 2021 20:27:49 GMT

Note that in this case, the order doesn't matter as well 😉

Re: How to assign colors to a column according to another column in PROC REPORT

Relax — Mon, 15 Mar 2021 20:36:23 GMT

Yes, I noticed that. So it may be easier for me to memorize and practice it the next time when I use PROC Report😀