Proc report using formatting (Background colour)

[YNWA1]

Hi,

I am using proc report to display pools of values. i want to highlight records that have change month on month.

Can anyone provide a solution to the below

1.If value on previous month > current month then Background colour is Green

2.If value on previous month < current month then Background colour is Red

3.If value on previous month = current month then Background colour is Yellow

data MY_SAMPLE;
infile datalines dlm=",";
format business_date date9.;
input BUSINESS_DATE :date9.
      EXCEPTION_CODE :$11.	
      EXCEPTION	:$16.
      LOB :$1.	
      MISSING_PCNT_CNT 	
      DIFF_0_5_PCNT_CNT	
      DIFF_5_15_PCNT_CNT	
      DIFF_15_50_PCNT_CNT;
datalines;
31Aug2020,EXP1,Exception 1,A,12,0,43,156
30Sep2020,EXP1,Exception 1,A,10,0,44,141
31Oct2020,EXP1,Exception 1,A,6,0,44,121
30Nov2020,EXP1,Exception 1,A,8,0,51,126
31Dec2020,EXP1,Exception 1,A,8,0,55,133
31Jan2021,EXP1,Exception 1,A,8,0,56,132
31Aug2020,EXP1,Exception 1,B,2,0,4,9
30Sep2020,EXP1,Exception 1,B,1,0,2,2
31Oct2020,EXP1,Exception 1,B,1,0,1,5
30Nov2020,EXP1,Exception 1,B,1,0,0,5
31Dec2020,EXP1,Exception 1,B,1,0,1,5
31Jan2021,EXP1,Exception 1,B,1,0,3,8
31Aug2020,EXP1,Exception 1,C,1,0,3,5
30Sep2020,EXP1,Exception 1,C,1,0,3,5
31Oct2020,EXP1,Exception 1,C,1,0,3,5
30Nov2020,EXP1,Exception 1,C,1,0,3,6
31Dec2020,EXP1,Exception 1,C,1,0,3,6
31Jan2021,EXP1,Exception 1,C,1,0,3,6
;
run;

proc report data=MY_SAMPLE nowd;
where exception_code='EXP1';
columns lob business_date
MISSING_PCNT_CNT
DIFF_0_5_PCNT_CNT
DIFF_5_15_PCNT_CNT
DIFF_15_50_PCNT_CNT;
define lob/noprint;
label business_date ='Month'
MISSING_PCNT_CNT='One Field Missing'
DIFF_0_5_PCNT_CNT='% Diff 0 to 5'
DIFF_5_15_PCNT_CNT='% Diff 5 to 15'
DIFF_15_50_PCNT_CNT='% Diff 15 to 50'
;
/*define MISSING_PCNT_CNT/analysis sum "" ;*/
by lob;
run;

What i want to achieve

[Kurt_Bremser]

Do a Google search for "sas proc report traffic lighting", and you'll find lots of resources dealing with this.

[Ksharp · Accepted solution]

Here is an example . Another two variables you can make it as my code .

data MY_SAMPLE;
infile datalines dlm=",";
format business_date date9.;
input BUSINESS_DATE :date9.
      EXCEPTION_CODE :$11.	
      EXCEPTION	:$16.
      LOB :$1.	
      MISSING_PCNT_CNT 	
      DIFF_0_5_PCNT_CNT	
      DIFF_5_15_PCNT_CNT	
      DIFF_15_50_PCNT_CNT;
datalines;
31Aug2020,EXP1,Exception 1,A,12,0,43,156
30Sep2020,EXP1,Exception 1,A,10,0,44,141
31Oct2020,EXP1,Exception 1,A,6,0,44,121
30Nov2020,EXP1,Exception 1,A,8,0,51,126
31Dec2020,EXP1,Exception 1,A,8,0,55,133
31Jan2021,EXP1,Exception 1,A,8,0,56,132
31Aug2020,EXP1,Exception 1,B,2,0,4,9
30Sep2020,EXP1,Exception 1,B,1,0,2,2
31Oct2020,EXP1,Exception 1,B,1,0,1,5
30Nov2020,EXP1,Exception 1,B,1,0,0,5
31Dec2020,EXP1,Exception 1,B,1,0,1,5
31Jan2021,EXP1,Exception 1,B,1,0,3,8
31Aug2020,EXP1,Exception 1,C,1,0,3,5
30Sep2020,EXP1,Exception 1,C,1,0,3,5
31Oct2020,EXP1,Exception 1,C,1,0,3,5
30Nov2020,EXP1,Exception 1,C,1,0,3,6
31Dec2020,EXP1,Exception 1,C,1,0,3,6
31Jan2021,EXP1,Exception 1,C,1,0,3,6
;
run;proc report data=MY_SAMPLE nowd;
where exception_code='EXP1';
columns lob business_date MISSING_PCNT_CNT DIFF_0_5_PCNT_CNT DIFF_5_15_PCNT_CNT DIFF_15_50_PCNT_CNT;
define lob/noprint;

define DIFF_15_50_PCNT_CNT/display;

label business_date ='Month'
MISSING_PCNT_CNT='One Field Missing'
DIFF_0_5_PCNT_CNT='% Diff 0 to 5'
DIFF_5_15_PCNT_CNT='% Diff 5 to 15'
DIFF_15_50_PCNT_CNT='% Diff 15 to 50';

compute DIFF_15_50_PCNT_CNT;
if not missing(lag) and lag>DIFF_15_50_PCNT_CNT then call define(_col_,'style','style={background=green}');
if not missing(lag) and lag=DIFF_15_50_PCNT_CNT then call define(_col_,'style','style={background=yellow}');
if not missing(lag) and lag<DIFF_15_50_PCNT_CNT then call define(_col_,'style','style={background=red}');
lag=DIFF_15_50_PCNT_CNT;
endcomp;


/*define MISSING_PCNT_CNT/analysis sum "" ;*/
by lob;
run;

[YNWA1]

Thanks Ksharp for solution...the lag function really help.