topic Re: Conditional formatting a column based on a threshold value using ODS in New SAS User

Conditional formatting a column based on a threshold value using ODS

SASFREAK — Tue, 09 Oct 2018 19:09:44 GMT

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?

Re: Conditional formatting a column based on a threshold value using ODS

Reeza — Tue, 09 Oct 2018 19:14:15 GMT

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.

Re: Conditional formatting a column based on a threshold value using ODS

SASFREAK — Tue, 09 Oct 2018 20:45:49 GMT

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.

Re: Conditional formatting a column based on a threshold value using ODS

Reeza — Tue, 09 Oct 2018 20:47:43 GMT

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.

Re: Conditional formatting a column based on a threshold value using ODS

SASFREAK — Wed, 10 Oct 2018 14:08:51 GMT

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/td-p/258959