How to Highlight a Cell Based on the Value of Another Cell

djbateman — Wed, 24 Feb 2021 17:15:39 GMT

I am trying to highlight a cell in an output table based on certain criteria. From the table below, I just want to output LINETEXT, RESULT, and PREF_RESULT. If RESULT is not the same as PREV_RESULT, then I want to highlight RESULT. In the compute block I have tried if RESULT^=PREF_RESULT or if DIFF_FLAG=1. If I compare RESULT and PREV_RESULT in the compute block, it highlights the entire column. If I use DIFF_FLAG=1, then I get a note that DIFF_FLAG is uninitialized and no highlighting happens. Can someone tell me what I am doing wrong?

As a side note, I'm also trying to bold any row that has LINENO=0, but I'm getting the same uninitialized message as with DIFF_FLAG, so I've tried a different method as shown in the code below. I would like to go back to the LINENO=0 logic if I can figure out the DIFF_FLAG.

SECNO	LINENO	DIFF_FLAG	LINETEXT	RESULT	PREV_RESULT
1	0	.	Safety
1	1	.	• Enrolled:	507	507
1	2	.	• Discontinued due to AE:	8	8
1	3	1	• Interruptions:	61 (1 LFT w/ Bili)	61 (14 LFT w/ Bili)
1	4	.	• Rash:	87 (15 Interruptions)	87 (15 Interruptions)
1	5	1	• LFT w/ Tbili:	1	77
1	6	1	• SAE:	127 (1 Deaths)	128 (1 Deaths)
2	0	.	Data Availability~{super 1}
2	1	1	• Visits Completed:	Week 100 - 267 (52.7%); Week 104 - 222 (43.8%)	Week 100 - 253 (49.9%); Week 104 - 215 (42.4%)
2	2	.	• ERT Spirometry Available:	Week 60 - 330 (65.1%); Week 72 - 250 (49.3%)	Week 60 - 330 (65.1%); Week 72 - 250 (49.3%)
2	3	1	• ICON Central Labs:	Week 60 - 373 (73.6%); Week 72 - 333 (65.7%)	Week 60 - 374 (73.8%); Week 72 - 335 (66.1%)
2	4	.	• Local Labs:	Week 60 - 14 (2.8%); Week 72 - 17 (3.4%)	Week 60 - 14 (2.8%); Week 72 - 17 (3.4%)
2	5	1	• Subjects without safety labs for >6 months:	39	41
3	0		Treatment-Emergent Transaminase Elevations
3	1		• Transaminase Elevation AEs:	74	74
3	2		• Transaminase Elevation SAEs:	5	5
3	3	1	• Transaminase Elevation Discontinuations:	3
3	4	1	• Transaminase Elevation Interruptions:	7

			title1 j=l "Executive Summary";
			footnote2 h=0.75 j=l '~{super 1} Visits shown are expressed as the earliest 2 visits with <80% visit rate.';
			proc report data=execsum;
				column diff_flag linetext result prev_result;
				define diff_flag / noprint;
				define linetext / display left 'Category';
				define result / display left "Current Response (&sysdate9.)";
				define prev_result / display left "Previous Response (&prev_date.)";
				compute linetext;
					if scan(linetext,1) in ('Safety','Data','Treatment') then call define(_row_,"style","style={font_weight=bold}");
				endcomp;
				compute result;
					if diff_flag=1 then call define(_col_,"style","style={background=yellow}");
				endcomp;
			run;
			footnote;

Re: How to Highlight a Cell Based on the Value of Another Cell

ballardw — Wed, 24 Feb 2021 17:51:56 GMT

Proc report builds reports left to right. So a compute block for a variable typically cannot see the result of a column to its right in the display.

Use a compute block for prev_result and use that to set the highlight using call define(results,<properties>)

Re: How to Highlight a Cell Based on the Value of Another Cell

djbateman — Wed, 24 Feb 2021 18:03:49 GMT

Oh my goodness! I figured it out!

I was simply leaving out "display" in the define statement for DIFF_FLAG. Don't you love when you spend half a day trying to find the one word that solves all your problems?

Here is the working code for those that are looking at this post to solve their own problem:

proc report data=execsum;
	column lineno diff_flag linetext result prev_result;
	define lineno / display noprint;
	define diff_flag / display noprint;
	define linetext / display left 'Category';
	define result / display left "Current Response (&sysdate9.)";
	define prev_result / display left "Previous Response (&prev_date.)";
	compute linetext;
		if lineno=0 then call define(_row_,"style","style={font_weight=bold}");
	endcomp;
	compute result;
		if diff_flag=1 then call define(_col_,"style","style={background=yellow}");
	endcomp;
run;

topic Re: How to Highlight a Cell Based on the Value of Another Cell in SAS Programming

How to Highlight a Cell Based on the Value of Another Cell

Re: How to Highlight a Cell Based on the Value of Another Cell

Re: How to Highlight a Cell Based on the Value of Another Cell