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
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;
Do a Google search for "sas proc report traffic lighting", and you'll find lots of resources dealing with this.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.