proc report compute statement / issue with a logic condition

ed_sas_member — Wed, 03 Jun 2020 07:34:52 GMT

Dear community,

A question as recently been posted: https://communities.sas.com/t5/New-SAS-User/Proc-Report-compute-if-then/m-p/652591#M22459

Basically, the idea was to highlight some cells with a specific background color when a condition was true.

However I can't explain myself why those two syntaxes are not equivalent: the first one works well, but not the other.

The only difference is that I specified abs(_C1_ - (_C2_ + _C3_)) > 0 instead of _C1_ ne (_C2_ + _C3_)

Please find below the code. NB: there are no missing values.

Any ideas?

Thanks a lot!

Best,

data TEST;
	input YEAR COL1 COL2 COL3;
	datalines;

2016 30 0 30
2017 20 10 9
2018 5 2 3
2019 10 4 4
2020 15 7 8
2021 5 0 5
;
run;

/* SYNTAX 1 - works well */
title "colour COL2 red if COL1 <> COL2+COL3";
proc report data=WORK.TEST nowd;
	column (YEAR, (COL1 COL2 COL3));
	define YEAR / across;
	define COL1 / analysis SUM missing;
	define COL2 / analysis SUM missing;
	define COL3 / analysis SUM missing;
	compute year;
		if  abs(_C1_ - (_C2_ + _C3_)) > 0 then call define('_c2_','style','style={background=red}');
		if  abs(_C4_ - (_C5_ + _C6_)) > 0 then call define('_c5_','style','style={background=red}');
		if  abs(_C7_ - (_C8_ + _C9_)) > 0 then call define('_c8_','style','style={background=red}');
		if  abs(_C10_ - (_C11_ + _C12_)) > 0 then call define('_c11_','style','style={background=red}');
		if  abs(_C13_ - (_C14_ + _C15_)) > 0 then call define('_c14_','style','style={background=red}');
		if  abs(_C16_ - (_C17_ + _C18_)) > 0 then call define('_c17_','style','style={background=red}');
	endcomp;
run;

/* SYNTAX 2 - issue? */
title "colour COL2 red if COL1 <> COL2+COL3";
proc report data=WORK.TEST nowd;
	column (YEAR, (COL1 COL2 COL3));
	define YEAR / across 'YEAR';
	define COL1 / analysis SUM 'COL1' missing;
	define COL2 / analysis SUM 'COL2' missing;
	define COL3 / analysis SUM 'COL3' missing;
	compute YEAR;

		if  _C1_ ne (_C2_ + _C3_) then call define('_c2_','style','style={background=red}');
		if  _C4_ ne (_C5_ + _C6_) then call define('_c5_','style','style={background=red}');
		if  _C7_ ne (_C8_ + _C9_) then call define('_c8_','style','style={background=red}');
		if  _C10_ ne (_C11_ + _C12_) then call define('_c11_','style','style={background=red}');
		if  _C13_ ne (_C14_ + _C15_) then call define('_c14_','style','style={background=red}');
		if  _C16_ ne (_C17_ + _C18_) then call define('_c17_','style','style={background=red}');

	endcomp;
run;

Re: proc report compute statement / issue with a logic condition

ChrisNZ — Wed, 03 Jun 2020 08:44:38 GMT

You don't specify how the results are different.

Could it be a matter of numeric precision when performing the calculations?

Re: proc report compute statement / issue with a logic condition

ed_sas_member — Wed, 03 Jun 2020 09:02:51 GMT

Hi @ChrisNZ

Here are the results when you run the code:

-> like the first display, the "second column" of each year should be highlighted when the sum of this column and the third one are not equal to the first one:

e.g. 2016 : 30 = 0 + 30 so no highlight

e.g. 2017 : 20 ≠ 10 + 9 so 10 is highlighted

Re: proc report compute statement / issue with a logic condition

Ksharp — Wed, 03 Jun 2020 11:16:17 GMT

compute YEAR;
---->
compute col3;

Re: proc report compute statement / issue with a logic condition

ed_sas_member — Wed, 03 Jun 2020 11:45:12 GMT

Thank you @Ksharp !

That works great +++

topic Re: proc report compute statement / issue with a logic condition in SAS Programming

proc report compute statement / issue with a logic condition

Re: proc report compute statement / issue with a logic condition

Re: proc report compute statement / issue with a logic condition

Re: proc report compute statement / issue with a logic condition

Re: proc report compute statement / issue with a logic condition