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;
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
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.
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/79063...)
Are there errors in the log? If so, show us the log.
If not, show us the code you used.
Also, you should use
name in ("Anna","Ian")
instead of
name in ("Ann","Ian")
Pay attention to details like that.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.