topic Proc Report-Color red/Green better/worse from month to month in SAS Programming

Proc Report-Color red/Green better/worse from month to month

Ronein — Fri, 19 May 2023 03:03:16 GMT

Hello
For each month there is a regression model that run and produce score (Y).
I would like to display the data set via proc report and apply a rule ad
S following:
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.
The calculation should be done for each row separately.
(Please note that green mean better explentaory variable or better resulted score)
(Please note that red mean worse explentaory variable or worse resulted score)
The colors will help the user (who look at the report) to understand if the customer had improvement or deterioration from one month to next month.

Please note that number of columns with nameYYMM can be different .

so i am looking for dynamic code that will work with any mumber of columns.

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

Re: Proc Report-Color red/Green better/worse from month to month

Ksharp — Fri, 19 May 2023 11:57:24 GMT

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

ods excel file='c:\temp\want.xlsx';
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;
ods excel close;

Re: Proc Report-Color red/Green better/worse from month to month

Ronein — Sat, 20 May 2023 05:38:57 GMT

Thanks,

Can you please explain:

The calculation is done on all columns except of the first column .

You wrote compute only on the last column.

How SAS know to perform computation on all columns (Except first)While you type computation only on last column?

compute &last.;

Re: Proc Report-Color red/Green better/worse from month to month

Ksharp — Sat, 20 May 2023 09:05:54 GMT

It is special character of PROC REPORT.
PROC REPORT calculated is from left to right and first to last.
Here I used "compute &last.;" which &last. is the last variable name, to make sure I can refer to any variable before &last.
if you use "compute var_name;" ,you only can refer to itself because there are not variables at left side of "var_name".

So I use ARRAY to refer to Mon2301 Mon2302 Mon2303 Mon2304 Mon2305
"array x{*} &list. ;"

do i=2 to dim(x);
means use "Mon2301 " (a.k.a x{1}) to compare with other variables to get the result you want and STYLE it by red or green.