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;
You don't specify how the results are different.
Could it be a matter of numeric precision when performing the calculations?
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
Thank you @Ksharp !
That works great +++
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.