Conditional Formatting using Proc Report

[YNWA1]

Hi, I need some help....

I want to use PROC REPORT in SAS to generate a report - Example attached

Report is a count of Exception by Types across Months (6 months)

If current month count > previous month count then apply background color to RED

If current month count< previous month count then apply background color to GREEN

If current month count= previous month count then apply background color to YELLOW

I was able to produce the report however the tricky part is how to apply the formatting

Thanks for helping

[andreas_lds · Accepted solution]

Welcome in the SAS community!

I won't open any office files, so i don't see the data you have.

You should post the code you have already written, please use "insert sas code" and don't attach it.

I am not sure, that comparing with previous values is possible in proc report.

[YNWA1]

hi andreas_Ids

My dataset looks like below

I want to achieve the below report

My code:

proc report data=TEST nowd;
columns exception business_date,Num_Excpt;
define exception/group "Total Number Each Month Exception Type";
define business_date/across order=data "Month";
define Num_Excpt/"";
run;

I can get the structure of the report with correct data, however i am struggling to apply the background colour. I tried using proc format but i can't apply it on the Num_Excpt field. I derived a new field to get the difference between current and previous month and my proc format is based on that calculated field.

Is there an easy way to achieve this using PROC and ODS?

[Kurt_Bremser]

Pictures are even worse than Excel files.

Please supply your data in a data step with datalines like this:

data have;
infile datalines dlm=",";
input num_excpt business_date :date9. exception_type :$10.;
format business_date date9.;
datalines;
312,31aug2020,Type A
;

This allows everybody to quickly recreate your dataset with a simple copy/paste and submit, without any doubts about data types, attributes and content.

Use this button:

to post logs or other fixed-format text, and the "little running man" right next to it for SAS code.

This is meant when we say "code box".

[YNWA1]

Thanx KurtBremser for your response...I am still getting use to the portal

I have inserted the datastep now

data TEST;
infile datalines dlm=",";
input num_excpt business_date :date9. exception_type :$10.;
format business_date date9.;
datalines;
312,31aug2020,Type A
283,30Sep2020,Type A
253,31Oct2020,Type A
270,30Nov2020,Type A
292,31Dec2020,Type A
292,31Jan2021,Type A
296,31Aug2020,Type B
310,30Sep2020,Type B
313,31Oct2020,Type B
299,30Nov2020,Type B
327,31Dec2020,Type B
303,31Jan2021,Type B
545,31Aug2020,Type C
553,30Sep2020,Type C
590,31Oct2020,Type C
550,30Nov2020,Type C
589,31Dec2020,Type C
617,31Jan2021,Type C
1697,31Aug2020,Type D
1784,30Sep2020,Type D
1813,31Oct2020,Type D
1864,30Nov2020,Type D
1905,31Dec2020,Type D
1953,31Jan2021,Type D

;
run;

proc report data=TEST nowd;
columns exception_type (business_date),Num_Excpt;
define exception_type/group "Total Number Each Month Exception Type";
define business_date/across order=data "Month";
define Num_Excpt/"";
run;

[PaigeMiller]

Thank you for providing the data in the desired form!

But you still need to take the suggestion above from @Kurt_Bremser and do a search for "proc report traffic lighting" and you will find example code for your PROC REPORT.

[Kurt_Bremser]

Hi!

Do a google search for "proc report traffic lighting", you'll find lots of resources dealing with conditional coloring of values in PROC REPORT and ODS.

[Ksharp]

It is show time for data step.

data TEST;
infile datalines dlm=",";
input num_excpt business_date :date9. exception_type :$10.;
format business_date date9.;
datalines;
312,31aug2020,Type A
283,30Sep2020,Type A
253,31Oct2020,Type A
270,30Nov2020,Type A
292,31Dec2020,Type A
292,31Jan2021,Type A
296,31Aug2020,Type B
310,30Sep2020,Type B
313,31Oct2020,Type B
299,30Nov2020,Type B
327,31Dec2020,Type B
303,31Jan2021,Type B
545,31Aug2020,Type C
553,30Sep2020,Type C
590,31Oct2020,Type C
550,30Nov2020,Type C
589,31Dec2020,Type C
617,31Jan2021,Type C
1697,31Aug2020,Type D
1784,30Sep2020,Type D
1813,31Oct2020,Type D
1864,30Nov2020,Type D
1905,31Dec2020,Type D
1953,31Jan2021,Type D
;
run;

proc sort data=test; by exception_type business_date ;run;
data test2;
 set test;
 by exception_type;
 length char_except $  200;
 char_except=put(num_excpt,best32. -l);
 if not first.exception_type and dif(num_excpt)>0 then char_except=cats('(*ESC*)S={background=red }',char_except);
 if not first.exception_type and dif(num_excpt)=0 then char_except=cats('(*ESC*)S={background=yellow}',char_except);
 if not first.exception_type and dif(num_excpt)<0 then char_except=cats('(*ESC*)S={background=green}',char_except);

run;


options orientation=landscape;
ods rtf file="c:\temp\temp.rtf" style=journal bodytitle;
proc report data=TEST2 nowd;
columns ("Total Number Each Month Exception Type" exception_type) ("Month" business_date),char_Except;
define exception_type/group ' ' style={cellwidth=30%};
define business_date/across order=data ' ' format=monyy7.;
define char_Except/group "";
run;
ods rtf close;

[YNWA1]

Hi Ksharp
Thanks a lot for providing a solution. I have one question though..what if i want to compute a grand total at the end of the report. I can't use the char_Except as it is of char type.

[Ksharp]

OK.Try this one .

data TEST;
infile datalines dlm=",";
input num_excpt business_date :date9. exception_type :$10.;
format business_date date9.;
datalines;
312,31aug2020,Type A
283,30Sep2020,Type A
253,31Oct2020,Type A
270,30Nov2020,Type A
292,31Dec2020,Type A
292,31Jan2021,Type A
296,31Aug2020,Type B
310,30Sep2020,Type B
313,31Oct2020,Type B
299,30Nov2020,Type B
327,31Dec2020,Type B
303,31Jan2021,Type B
545,31Aug2020,Type C
553,30Sep2020,Type C
590,31Oct2020,Type C
550,30Nov2020,Type C
589,31Dec2020,Type C
617,31Jan2021,Type C
1697,31Aug2020,Type D
1784,30Sep2020,Type D
1813,31Oct2020,Type D
1864,30Nov2020,Type D
1905,31Dec2020,Type D
1953,31Jan2021,Type D
;
run;




proc sql noprint;
select count(distinct put(business_date,monyy7.))+1 into :n
 from test;
quit;
data _null_;
length month $ 200;
 do i=2 to &n;
   month=catx(' ',month,cats('_C',i,'_'));
 end;
call symputx('month',month);
run;


proc report data=TEST nowd;
columns ("Total Number Each Month Exception Type" exception_type) ("Month" business_date),num_excpt;
define exception_type/group ' ' ;
define business_date/across order=data ' ' format=monyy7.;
define num_excpt/analysis sum "";
compute num_excpt;
 array x{*} &month ;
do i=2 to dim(x);
if x{i-1}<x{i} then call define(vname(x{i}),'style','style={background=red}');
if x{i-1}=x{i} then call define(vname(x{i}),'style','style={background=yellow}');
if x{i-1}>x{i} then call define(vname(x{i}),'style','style={background=green}');
 end;
endcomp;
compute after;
exception_type='GrandTotal';
endcomp;
rbreak after /summarize;
run;

[YNWA1]

Thanks KSharp. Is there an easy way to suppress formatting in the last row i.e Grand Total

[ballardw]

Like this?

proc report data=TEST nowd;
   columns ("Total Number Each Month Exception Type" exception_type) ("Month" business_date),num_excpt;
   define exception_type/group ' ' ;
   define business_date/across order=data ' ' format=monyy7.;
   define num_excpt/analysis sum "";
   compute num_excpt;
      array x{*} &month ;
      do i=2 to dim(x);
         if x{i-1}<x{i} then call define(vname(x{i}),'style','style={background=red}');
         if x{i-1}=x{i} then call define(vname(x{i}),'style','style={background=yellow}');
         if x{i-1}>x{i} then call define(vname(x{i}),'style','style={background=green}');
      end;
   endcomp;
   compute after ;
      exception_type='GrandTotal';
      do i=2 to dim(x);
         if x{i-1}<x{i} then call define(vname(x{i}),'style','style={background=white}');
         if x{i-1}=x{i} then call define(vname(x{i}),'style','style={background=white}');
         if x{i-1}>x{i} then call define(vname(x{i}),'style','style={background=white}');
      end;

   endcomp;
   rbreak after /summarize style={background=white};
run;

[Ksharp]

Sure.

proc report data=TEST nowd;
columns ("Total Number Each Month Exception Type" exception_type) ("Month" business_date),num_excpt;
define exception_type/group ' ' ;
define business_date/across order=data ' ' format=monyy7.;
define num_excpt/analysis sum "";
compute num_excpt;
 array x{*} &month ;
if missing(_break_) then do;
do i=2 to dim(x);
if x{i-1}<x{i} then call define(vname(x{i}),'style','style={background=red}');
if x{i-1}=x{i} then call define(vname(x{i}),'style','style={background=yellow}');
if x{i-1}>x{i} then call define(vname(x{i}),'style','style={background=green}');
 end;
end;
endcomp;
compute after;
exception_type='GrandTotal';
endcomp;
rbreak after /summarize;
run;