Conditional formatting a column based on a threshold value using ODS

[SASFREAK]

I have a task where I need to highlight a cell in an html output if its value falls outside a range (threshold value). The problem is, I have concatenated a value and its %Chg into a single variable for a better representation of the results in an output table. However, I need to highlight the cells based on the threshold values. Since, this new variable is a string (Character) it's not possible (or at least I don't know) to conditional format them on a basis of some numeric value. 

				diff[i] = comp[i]-base[i];
			 if base[i] > 0 then 
				pct_chg[i] = diff[i]/base[i] *100 ;
			if pct_chg[i]=. then pct_chg[i]=0;
			BASE_C[i] = right(put(diff[i],best.))|| "( " || strip(put(pct_chg[i],best.)) ||" % )"; 

Difference (%Change) section has columns which I need to format based on a threshold value (see attached). For example, if difference value or PCT value falls outside threshold say 5% then change the cell color to Red. Column "M 201707" under Difference (%Change) is created by using 2 variables, 

Where, diff & pct_chg are numeric variables. Is there any way to accomplish this task, may be early in the code or in proc report?

 

[Reeza · Accepted solution]

Add a variable that is 0/1 if the record needs to be flagged in the data step and then apply the conditional format based on that column. You can use it in PROC REPORT and not display it with the NODISPLAY option.

[SASFREAK]

Hi Reeza,

As you have said, I've created ab binary variable ind = ( pct_chg_lst < &pct_threshold.); where &pct_threshold =5 ( say 5% is my threshold. Now, in my proc report when I'm trying to use this binary variable , I am getting variable uninitialized message.

		define BASE_C_LAST / " % Chg #"  	style={cellwidth= 0.60 in};

		COMPUTE BASE_C_LAST;
			if IND= 0 THEN CALL DEFINE("BASE_C_LAST", "style", "STYLE=[BACKGROUND=LAVENDERBLUSH]");
		ENDCOMP; 

I am trying to change color of variable BASE_C_LAST based on the value of IND. 

[Reeza]

Did you remember to define it in your COLUMN statement? And before the column needed - location matters for COMPUTE variables.

---

@SASFREAK wrote:

Hi Reeza,

As you have said, I've created ab binary variable ind = ( pct_chg_lst < &pct_threshold.); where &pct_threshold =5 ( say 5% is my threshold. Now, in my proc report when I'm trying to use this binary variable , I am getting variable uninitialized message.

		define BASE_C_LAST / " % Chg #"  	style={cellwidth= 0.60 in};

		COMPUTE BASE_C_LAST;
			if IND= 0 THEN CALL DEFINE("BASE_C_LAST", "style", "STYLE=[BACKGROUND=LAVENDERBLUSH]");
		ENDCOMP; 

I am trying to change color of variable BASE_C_LAST based on the value of IND. 

---

[SASFREAK]

I also find one paper (link) which explained usage of binary variable which quote "In addition, any numeric variable without a usage of ORDER, GROUP or DISPLAY has a default usage of SUM...and that means that the correct reference for VAR4 (and for your NOPRINT items) is variable_name.SUM in the COMPUTE block."


https://communities.sas.com/t5/SAS-Programming/PROC-REPORT-SAS-log-reporting-dataset-variables-are/t...