Proc Report: compute if then

[MART1]

Hello everybody

I'm trying to apply a format to some cells in Proc Report based on their value (in the example I've provided, the TOT for 2010,2013,2015 and 2016 should be blue, while the others remain white).

Here is the code I'm using but I can't get it to work

data TEST;
	input YEAR EXPECTED MISSED TOTAL;
	datalines;

2010  10 20 30
2011  11 8 25
2012  9 15 11
2013  14 5 19
2014  5 8  11
2015  7 9 16
2016  10 1 11
2017  15 2 19
;
run;

proc report data=WORK.TEST nowd;
	column (YEAR, (EXPECTED MISSED TOTAL));
	define YEAR / across 'YEAR';
	define EXPECTED / analysis SUM 'EXP' missing;
	define MISSED / analysis SUM 'MISS' missing;
	define TOTAL / analysis SUM 'TOT' missing;

	compute YEAR;
		if EXPECTED + MISSED = TOTAL then
			do;
				do i = 3 to 24 by 3;
					call define(i,'style','style={background=lightblue}');
				end;
			end;
	endcomp;
run;

quit;

I've tried different combination of fields in the compute, but I can't get it to work.

many thanks

[ed_sas_member]

Hi @MART1 

I am afraid this does not work because "i" is not a macrovariable and you cannot refer directly to each new columns created by the "ACROSS" definition.

Please try this:

proc report data=WORK.TEST nowd;
	column (YEAR, (EXPECTED MISSED TOTAL));
	define YEAR / across 'YEAR';
	define EXPECTED / analysis SUM 'EXP' missing;
	define MISSED / analysis SUM 'MISS' missing;
	define TOTAL / analysis SUM 'TOT' missing;
	compute YEAR;
		if (_C1_ + _c2_) = _c3_ then call define('_c3_','style','style={background=lightblue}');
		if (_C4_ + _c6_) = _c6_ then call define('_c6_','style','style={background=lightblue}');
		if (_C7_ + _c8_) = _c9_ then call define('_c9_','style','style={background=lightblue}');
		if (_C10_ + _c11_) = _c12_ then call define('_c12_','style','style={background=lightblue}');
		if (_C13_ + _c14_) = _c15_ then call define('_c15_','style','style={background=lightblue}');
		if (_C16_ + _c17_) = _c18_ then call define('_c18_','style','style={background=lightblue}');
		if (_C19_ + _c20_) = _c21_ then call define('_c21_','style','style={background=lightblue}');
		if (_C22_ + _c23_) = _c24_ then call define('_c24_','style','style={background=lightblue}');
	endcomp;
run;

 

[MART1]

thank you @ed_sas_memeber

It would be a good solution, however my data adds new data on a daily basis (I'm using Year as a mock example, in reality they are Day, 60 of them),
so I guess it would be unpractical to hard code for all the cells.

I've also tried to ad a fourth column to the dataset (not to be displayed in the report) indicating Y or N whether the sum matches or not, but I can't use it properly in the proc report...

thanks
a "Year" on a daily basis (I'm using Year as a mock example, in reality they are Day) and have

[ed_sas_member]

In this case, maybe a PROC TABULATE could be a more flexible solution?

[MART1 · Accepted solution]

Hello @ed_sas_member 

can I just ask one more thing about the solution you have proposed.

If I need to fill the background of a different cell rather than the last one in the group

i.e.    IF C1  <>  (C2+C3)        I need to colour C2

it does not work, as it fills the cell regardless

Below is an example

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;

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;

Is there any rule / and a wokaround for this?

(it seems that only the "define" can only be applied to the last cell in the group?)

Many thanks

[ed_sas_member]

Hi @MART1 

The issue seems to come from the way to specify the condition. E.g.  _C1_ ne (_C2_ + _C3_)

It works if you put this instead: abs(_C1_ - (_C2_ + _C3_)) > 0

Very weird indeed 🙄

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;

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;

Output: 

Hope this helps,

Best,

[ed_sas_member]

Hi @MART1 

Another option is just to put  "compute COL3;" instead of  "compute YEAR;"

All the best,

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 COL3;

		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;

[MART1]

Thanks @ed_sas_member 

weird indeed, it can handle a more complex math operation but not the simple one!

But your version works a treat, very helpful many thanks

[MART1]

PS: I have incorrectly tagged my post a solution

The solution is  

abs(_C1_ - (_C2_ + _C3_)) > 0