topic Re: Excel Conditional Formatting in SAS Programming

Excel Conditional Formatting

Eugenio211 — Mon, 12 Jul 2021 15:09:30 GMT

Hello, is it possible to do a 'conditional formatting' of values (lowest to highest) in proc report? thanks.

Light to dark color range.

Conditional Formatting

Re: Excel Conditional Formatting

ballardw — Mon, 12 Jul 2021 15:16:24 GMT

Generic answer: Yes.

A format can be applied to a style element based on the value displayed, such as backgroundcolor. The documentation for Proc report has some limited examples. If you want each value to have its own background color you will need to spend some time to get the values and create a format. Pretty easy to create a format for ranges of values, such as each 5, or 5%.

For more details in your case provide example data in the form of a data step, the proc report code and rules for coloring.

Re: Excel Conditional Formatting

Eugenio211 — Mon, 12 Jul 2021 15:27:09 GMT

here are the codes that I am using.

Data Step:

Data Test1; set Test;
Current_Total = Current_PDL_IL_Count + Current_LOC_Count;
Perc_of_Book_Converted = Current_LOC_Count/Launch_Date_PDL_IL_Count;
Drop_in_PDL_IL = Current_PDL_IL_Count - Launch_Date_PDL_IL_Count;
Perc_Drop_in_PDL_IL = Drop_in_PDL_IL / Launch_Date_PDL_IL_Count;
Increase_Overall = Current_Total - Launch_Date_PDL_IL_Count;
Perc_Increase = Increase_Overall/Launch_Date_PDL_IL_Count;
run;

Proc Report:

proc report data=Test1;
columns rd dm location Launch_Date_PDL_IL_Count Current_PDL_IL_Count
Current_LOC_Count Current_Total Perc_of_Book_Converted
Drop_in_PDL_IL Perc_Drop_in_PDL_IL Increase_Overall Perc_Increase;
define rd/'RD' style(column)=[cellwidth=3cm];
define dm/'DM' style(column)=[cellwidth=3cm];
define Location/group 'Location';
define Launch_Date_PDL_IL_Count/'Launch Date PDL Count';
define Current_PDL_IL_Count/'Current PDL Count';
define Current_LOC_Count/'Current LOC Count';
define Current_Total/'Current Total';
define Perc_of_Book_Converted/'%of Book Converted' Format= Percent8.2;
define Drop_in_PDL_IL/'Drop in PDL';
define Perc_Drop_in_PDL_IL/'%Drop in PDL' Format= Percent8.2;
define Increase_Overall/'Increase Overall';
define Perc_Increase/'%Increase' Format= Percent8.2;
run;

I want to do the conditional formatting in the '%Increase' column.