topic Re: Proc Report - How to color code certain cell or column? in SAS Programming

Proc Report - How to color code certain cell or column?

annaleticia — Tue, 19 Apr 2022 15:47:09 GMT

Hello,

I need assistance on how to color code specific cells using PROC REPORT. I'm trying to resolve my code below as the logic is not reading a certain text variable NAME IN (‘Ann’,’Ian’). Please assist.

proc report data=TEST nowd;
TITLE1 j=left "Sample Report
		column ID  ICT_DT  POST_RISK_LEVEL;
		define INIT_DATE/display;
		define NAME/display;
		
compute ICT_DT; 
            if INIT_DATE=. then call define(_col_,"style","style={background=RED}");
			else
			if INIT_DATE =. AND NAME IN (‘Ann’,’Ian’) then call define(_col_,"style","style={background=WHITE}"); /*NOT WORKING*/
			endcomp;
run;

Re: Proc Report - How to color code certain cell or column?

PaigeMiller — Tue, 19 Apr 2022 16:08:22 GMT

Two possible causes:

NAME IN (‘Ann’,’Ian’)

get rid of the curly quotes, make sure you are using straight quotes

Also, your logic is flawed, the code

else if INIT_DATE =. AND NAME IN (‘Ann’,’Ian’)

never executes. Why? Because first you test to see if INIT_DATE=. and the logic never gets to the else if part of the code for the three rows shown in your desired output.

Re: Proc Report - How to color code certain cell or column?

Cynthia_sas — Tue, 19 Apr 2022 16:29:00 GMT

Hi:
In addition to the above suggestions, I am not sure that your code produces the results you show. You do NOT have NAME in the COLUMN statement. Typically, PROC REPORT works from Left to right in setting the style. You show 4 variables in your current COLUMN statement, but you only show 3 columns in your screen shot of the output. We need to see ALL of your code, not just parts of the code. ALL the DEFINE statements, the entire COLUMN statement and ALL of the compute blocks.
Cynthia

Re: Proc Report - How to color code certain cell or column?

annaleticia — Tue, 19 Apr 2022 17:23:27 GMT

Hello, Here's the updated code --- the syntax still not reading the NAME column. proc report data=TEST nowd; TITLE1 j=left "Sample Report column ID INIT_DATE NAME ; define INIT_DATE/display; define NAME/display; compute INIT_DATE; if INIT_DATE =. AND NAME IN ("Ann","Ian") then call define(_col_,"style","style={background=WHITE}"); /*NOT WORKING*/ else if INIT_DATE=. AND NAME IN ("Tom") then call define(_col_,"style","style={background=RED}"); /*NOT WORKING*/ endcomp; run;

Re: Proc Report - How to color code certain cell or column?

annaleticia — Tue, 19 Apr 2022 17:24:51 GMT

Hello,

Here's the updated code --- the syntax still not reading the NAME column.

proc report data=TEST nowd;
TITLE1 j=left "Sample Report
column ID INIT_DATE NAME ;
define INIT_DATE/display;
define NAME/display;

compute INIT_DATE;
if INIT_DATE =. AND NAME IN ("Ann","Ian") then call define(_col_,"style","style={background=WHITE}"); /*NOT WORKING*/
else if INIT_DATE=. AND NAME IN ("Tom") then call define(_col_,"style","style={background=RED}"); /*NOT WORKING*/
endcomp;
run;

Re: Proc Report - How to color code certain cell or column?

PaigeMiller — Tue, 19 Apr 2022 17:35:06 GMT

Please paste your code into the code window from now on. The code window appears when you click on the "little running man" icon.

You would help yourself, and help us help you, by properly indenting your code as well, as I show below.

Give this a try:

proc report data=test nowd;
    title1 j=left "Sample Report";
    column id init_date name _dummy;
    define init_date/display;
    define name/display;
    define _dummy/noprint;

    compute _dummy;
        if init_date =. and name in ("Ann","Ian") then call define('init_date',"style","style={background=WHITE}"); 
        else if init_date=. and name in ("Tom") then call define('init_date',"style","style={background=RED}"); 
    endcompute;
run;

Thanks to @BrunoMueller for the idea to use _DUMMY to help with coloring columns (see: https://communities.sas.com/t5/SAS-Programming/Alternate-column-coloring-using-proc-report/m-p/790639#M253161)

Re: Proc Report - How to color code certain cell or column?

PaigeMiller — Tue, 19 Apr 2022 18:09:11 GMT

Also, you should use

name in ("Anna","Ian")

instead of

name in ("Ann","Ian")

Pay attention to details like that.

Re: Proc Report - How to color code certain cell or column?

annaleticia — Tue, 19 Apr 2022 18:47:18 GMT

Hello,
For some reason, the program is still not reading the color coding for the name variable.
*name in ("Ann","Ian")

Re: Proc Report - How to color code certain cell or column?

PaigeMiller — Tue, 19 Apr 2022 18:49:23 GMT

Are there errors in the log? If so, show us the log.

If not, show us the code you used.

Re: Proc Report - How to color code certain cell or column?

Cynthia_sas — Tue, 19 Apr 2022 18:50:42 GMT

Hi:

Again, you cannot test for NAME in the COMPUTE block for INIT_DATE because of the PROC REPORT "left-to-right" rule. As shown in this output:

My report #1 was generated with some fake data and a version of your code with NAME appearing AFTER INIT_DATE in the COLUMN statement. This will not work. However, in my report #2, I used the _DUMMY helper variable technique that was suggested. This is a timing issue based on the left-to-right rule. In your code at the point in time when INIT_DATE is being placed on the report row, the COMPUTE block doesn't know what the value for NAME is because NAME is not yet visible on the report row. PROC REPORT handles each COMPUTE block as the variable is placed on the report row. So when INIT_DATE is being placed and the COMPUTE block is being executed, PROC REPORT does not have any value for NAME yet. Remember the COMPUTE block is building the report row, line by line, column by column.

In my #2 report, I did some additional highlighting with different colors so you could see the way that each variable on the report row can be touched in a different COMPUTE block.

Here's my code:

There have been many previous forum postings about PROC REPORT and the left-to-right rule. I'd recommend looking at some of those previous postings.

Cynthia

Re: Proc Report - How to color code certain cell or column?

annaleticia — Tue, 19 Apr 2022 19:12:22 GMT

Thank you Cynthia!