topic Re: Proc Report-Color red/Green better/worse +ignore missing values in SAS Programming

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

Ronein — Tue, 23 May 2023 10:49:00 GMT

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;

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

PaigeMiller — Tue, 23 May 2023 11:14:26 GMT

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.

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

Ronein — Tue, 23 May 2023 11:28:34 GMT

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}');

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

PaigeMiller — Tue, 23 May 2023 11:31:17 GMT

What about it didn't work? Explain.

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

Ronein — Tue, 23 May 2023 11:35:14 GMT

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

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

PaigeMiller — Tue, 23 May 2023 12:23:06 GMT

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?

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

Ronein — Tue, 23 May 2023 16:14:09 GMT

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;

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

Ronein — Tue, 23 May 2023 16:15:42 GMT

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;