topic Re: Conditional Formatting using Proc Report in SAS Programming

Conditional Formatting using Proc Report

YNWA1 — Tue, 24 Aug 2021 07:08:02 GMT

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

Re: Conditional Formatting using Proc Report

andreas_lds — Tue, 24 Aug 2021 07:26:34 GMT

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.

Re: Conditional Formatting using Proc Report

Kurt_Bremser — Tue, 24 Aug 2021 08:30:58 GMT

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.

Re: Conditional Formatting using Proc Report

YNWA1 — Tue, 24 Aug 2021 08:47:11 GMT

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?

Re: Conditional Formatting using Proc Report

Kurt_Bremser — Tue, 24 Aug 2021 09:17:51 GMT

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".

Re: Conditional Formatting using Proc Report

YNWA1 — Tue, 24 Aug 2021 11:32:24 GMT

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;

Re: Conditional Formatting using Proc Report

PaigeMiller — Tue, 24 Aug 2021 11:40:10 GMT

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.

Re: Conditional Formatting using Proc Report

Ksharp — Tue, 24 Aug 2021 12:49:39 GMT

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;

Re: Conditional Formatting using Proc Report

YNWA1 — Wed, 25 Aug 2021 00:54:49 GMT

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.

Re: Conditional Formatting using Proc Report

Ksharp — Wed, 25 Aug 2021 11:46:38 GMT

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;

Re: Conditional Formatting using Proc Report

YNWA1 — Wed, 25 Aug 2021 13:04:56 GMT

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

Re: Conditional Formatting using Proc Report

ballardw — Wed, 25 Aug 2021 16:31:18 GMT

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;

Re: Conditional Formatting using Proc Report

Ksharp — Thu, 26 Aug 2021 11:36:06 GMT

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;