Proc Report-Color red/Green better/worse +ignore missing values

[Ronein]

Hello

I want to create proc report and display the data and apply color by following rule:

If there is increase from one month to next month then color it in red.
If there is decrease from one month to next month then color it in green.

If in one month have null value and next month non-null value then DON'T  color it.

What is the way to  do it please?

expected result

Here is raw data and my try

Data have;
infile datalines delimiter=',';
input mon1 mon2 mon3 mon4 ;
cards;
1,.,2,2
4,.,.,1
3,4,1,1
5,3,3,1
6,2,4,5
;
Run;

proc transpose data=have(obs=0) out=vname;
var _all_;
run;


data _null_;
set vname(firstobs=2) end=last;
length list $ 2000;
retain list;
list=catx(' ',list,_name_);
if last then do;
  call symputx('list',list);
  call symputx('last',_name_);
end;
run;
%put &list.;
%put &last.;


proc report data=have nowd;
define _all_/display;
compute &last.;
array x{*} &list. ;
do i=2 to dim(x);
if x{i}>x{i-1} then call define(vname(x{i}),'style','style={background=red}');
if x{i}<x{i-1} then call define(vname(x{i}),'style','style={background=green}');
end;
endcomp;
run;

[PaigeMiller]

if x{i}>x{i-1} then call define(vname(x{i}),'style','style={background=red}');
if x{i}<x{i-1} then call define(vname(x{i}),'style','style={background=green}');

You can modify these IF statements to ignore the case where one month is null and the next month is not null. Please try it and show us what you have come up with.

[Ronein]

Sure, I know that the code modification should be done  here

if x{i}>x{i-1} then call define(vname(x{i}),'style','style={background=red}');
if x{i}<x{i-1} then call define(vname(x{i}),'style','style={background=green}');

The question is how to do it

I tried this code but it didnt work well

if x{i}>x{i-1} AND x{i-1} ne . then call define(vname(x{i}),'style','style={background=red}');
if x{i}<x{i-1}  AND x{i-1} ne . then call define(vname(x{i}),'style','style={background=green}');

[PaigeMiller]

What about it didn't work? Explain.

[Ronein]

some cells should be colored  but in my code they are not

[PaigeMiller]

Sure. You changed the structure of the data (specifically you removed a column) that was provided by @Ksharp in your earlier thread, and the code has to change as well to reflect the different data.

Here's the data from @Ksharp 

Data Have;
Input VAR_name $ Mon2301 Mon2302 Mon2303 Mon2304 Mon2305;
cards;
X1 70 70 70 70 70
X2 70 70 90 90 90
X3 40 40 40 40 40
X4 30 30 50 50 20
X5 70 45 45 45 45
X6 30 30 30 20 40
TOTAL 310 285 325 315 305
Y 7 6 9 9 7
;

Here's the data you provided.

Data have;
infile datalines delimiter=',';
input mon1 mon2 mon3 mon4 ;
cards;
1,.,2,2
4,.,.,1
3,4,1,1
5,3,3,1
6,2,4,5
;

There's an important difference here, that your data has no identifier in the first column. Earlier there was an identifier variable. So, without the identifier variable, you need to modify the code.

 set vname(firstobs=2) end=last;

is no longer correct. The firstobs=2 removes the identifier variable name from the list of variable names.  Since you don't have an identifier variable, what change would you need to make to the above?

[Ronein]

Still result is not as desired

Data have;
infile datalines delimiter=',';
input mon1 mon2 mon3 mon4 ;
cards;
1,.,2,2
4,.,.,1
3,4,1,1
5,3,3,1
6,2,4,5
;
Run;

proc transpose data=have(obs=0) out=vname;
var _all_;
run;


data _null_;
set vname(firstobs=1) end=last;
length list $ 2000;
retain list;
list=catx(' ',list,_name_);
if last then do;
  call symputx('list',list);
  call symputx('last',_name_);
end;
run;
%put &list.;
%put &last.;


proc report data=have nowd;
define _all_/display;
compute &last.;
array x{*} &list. ;
do i=2 to dim(x);
if x{i}>x{i-1} AND x{i-1} ne . then call define(vname(x{i}),'style','style={background=red}');
if x{i}<x{i-1}  AND x{i-1} ne . then call define(vname(x{i}),'style','style={background=green}');
end;
endcomp;
run;

[Ronein]

This code provide the solution,thanks

Data have;
infile datalines delimiter=',';
input mon1 mon2 mon3 mon4 ;
cards;
1,.,2,2
4,.,.,1
3,4,1,1
5,3,3,1
6,2,4,5
;
Run;

proc transpose data=have(obs=0) out=vname;
var _all_;
run;


data _null_;
set vname(firstobs=1) end=last;
length list $ 2000;
retain list;
list=catx(' ',list,_name_);
if last then do;
  call symputx('list',list);
  call symputx('last',_name_);
end;
run;
%put &list.;
%put &last.;


proc report data=have nowd;
define _all_/display;
compute &last.;
array x{*} &list. ;
do i=2 to dim(x);
if x{i}>x{i-1} AND x{i-1} ne . and x{i} ne . then call define(vname(x{i}),'style','style={background=red}');
if x{i}<x{i-1}  AND x{i-1} ne . and x{i} ne . then call define(vname(x{i}),'style','style={background=green}');
end;
endcomp;
run;