PROC Report Conditional Formatting

[david27]

Hello,

I have this:

The code does NOT highlight specific cells PINK. But the entire row.

I want only the negative cells highlighted pink.

Please advise.
What am I missing here?

DATA have;
INFILE datalines dlm="|" MISSOVER TRUNCOVER;
INPUT Partner :$8. Year :$4. Source :$11. GroupS :$8. M01 :8. M02 :8. M03 :8. M04 :8. M05 :8. M06 :8. M07 :8. M08 :8. M09 :8. M10 :8. M11 :8. M12 :8. YTD :8. ;
datalines;
CARE|2021|DMV|N/A|0|0|0|26|-24|0|-13|-1|2|3|5|-1|10
CARE|2021|DMV-DIPER|ADULT|0|0|0|433|398|444|484|534|367|466|501|366|10
CARE|2021|DMV-DIPER|BCCTP|0|0|0|0|0|-1|0|0|0|1|0|0|10
CARE|2021|DMV-DIPER|CHILD|0|0|0|535|513|524|340|185|267|183|367|193|10
CARE|2021|DMV-DIPER|LTC|0|0|0|9|-2|7|-1|4|4|-5|-3|-3|10
CARE|2021|DMV-DIPER|N/A|0|0|0|0|0|0|0|-1|0|0|0|0|7
CARE|2021|DMV-DIPER|OE|0|0|0|1021|977|767|697|613|641|398|485|635|10
CARE|2021|DMV-DIPER|ABC|0|0|0|86|160|169|243|188|118|149|83|112|10
CARE|2021|DIPER|ADULT|0|0|0|13|-6|-2|-2|2|-6|4|0|-8|10
CARE|2021|DIPER|CHILD|0|0|0|19|-18|-8|4|-15|1|-7|3|-14|10
CARE|2021|DIPER|LTC|0|0|0|-1|-1|-1|0|0|0|1|0|1|10
CARE|2021|DIPER|N/A|0|0|0|0|0|0|1|-1|0|0|0|0|6
CARE|2021|DIPER|OE|0|0|0|55|-64|4|7|-9|-9|10|-4|7|10
CARE|2021|DIPER|ABC|0|0|0|-18|-11|-3|-18|3|-3|6|1|-1|10
CARE|2021|MMM|X|0|0|0|-11|9|3|54|26|8|9|-26|0|10
CARE|2022|DMV|N/A|-26|67|-2|-6|-2|4|3|7|0|12|-7|-5|12
CARE|2022|DMV-DIPER|ADULT|346|400|470|368|508|513|635|222|415|505|341|446|12
CARE|2022|DMV-DIPER|BCCTP|0|0|0|0|0|0|-1|0|0|0|0|0|12
CARE|2022|DMV-DIPER|CHILD|180|233|381|403|541|436|269|-65|95|400|289|216|12
CARE|2022|DMV-DIPER|LTC|-5|-6|-5|0|2|-7|-8|7|0|3|4|0|12
CARE|2022|DMV-DIPER|N/A|0|6|7|18|18|13|7|8|17|20|15|7|11
CARE|2022|DMV-DIPER|OE|584|795|890|989|854|800|450|588|528|677|678|629|12
CARE|2022|DMV-DIPER|ABC|52|-43|151|77|98|149|108|77|82|125|167|89|12
CARE|2022|DIPER|ADULT|1|35|-31|-5|4|4|0|-3|35|6|2|-11|12
CARE|2022|DIPER|CHILD|-11|-10|-2|-3|2|2|9|2|21|-1|-11|-8|12
CARE|2022|DIPER|LTC|-1|0|1|-2|0|0|0|0|0|1|0|0|6
CARE|2022|DIPER|N/A|0|0|0|0|0|0|0|1|1|0|-1|0|3
CARE|2022|DIPER|OE|6|-4|6|-3|2|7|-6|-2|10|-20|1|1|12
CARE|2022|DIPER|ABC|9|33|-23|-3|-3|2|-12|3|24|32|-54|-7|12
CARE|2022|MMM|X|-1|-36|5|-20|18|18|38|32|-17|3|0|1|12
CARE|2023|DMV|N/A|155|2665|0|0|0|0|0|0|0|0|0|0|2
CARE|2023|DMV-DIPER|ADULT|494|344|0|0|0|0|0|0|0|0|0|0|2
CARE|2023|DMV-DIPER|BCCTP|2|-3|0|0|0|0|0|0|0|0|0|0|2
CARE|2023|DMV-DIPER|CHILD|10|449|0|0|0|0|0|0|0|0|0|0|2
CARE|2023|DMV-DIPER|LTC|212|-16|0|0|0|0|0|0|0|0|0|0|2
CARE|2023|DMV-DIPER|N/A|9|8|0|0|0|0|0|0|0|0|0|0|2
CARE|2023|DMV-DIPER|OE|960|922|0|0|0|0|0|0|0|0|0|0|2
CARE|2023|DMV-DIPER|ABC|11291|223|0|0|0|0|0|0|0|0|0|0|2
CARE|2023|DIPER|ADULT|-15|-27|0|0|0|0|0|0|0|0|0|0|2
CARE|2023|DIPER|CHILD|30|-69|0|0|0|0|0|0|0|0|0|0|2
CARE|2023|DIPER|LTC|0|7|0|0|0|0|0|0|0|0|0|0|2
CARE|2023|DIPER|OE|13|-12|0|0|0|0|0|0|0|0|0|0|2
CARE|2023|DIPER|ABC|64|143|0|0|0|0|0|0|0|0|0|0|2
CARE|2023|MMM|X|-44|-193|0|0|0|0|0|0|0|0|0|0|2
;run;
PROC Sort data=have;
by Partner Year Source GroupS M01 M02 M03 M04 M05 M06 M07 M08 M09 M10 M11 M12 YTD;;
run;
%let OutExcel=/path/on/unix/server/summary.xlsx;
	
ods _all_ close;
goptions device=actximg;
ods excel  file="&OutExcel." options(embedded_titles='on');


ods excel options(sheet_interval='NONE' Sheet_name='DMV-DIPER Match');

proc report data=have spanrows split='*'  style={BORDERCOLOR=DARK1 BORDERWIDTH=1}  style(column header)=[verticalalign=top];
	Columns Partner Year Source GroupS M01 M02 M03 M04 M05 M06 M07 M08 M09 M10 M11 M12 YTD;
	Define Partner			/ 'Partner' Group;
	Define Year				/ 'Year' Group;
	Define Source			/ 'Source' Group;
	Define GroupS			/ 'GroupS' Group;
Define M01 / 'Jan' sum format=NumFmt. ;
Define M02 / 'Feb' sum format=NumFmt. ;
Define M03 / 'Mar' sum format=NumFmt. ;
Define M04 / 'Apr' sum format=NumFmt. ;
Define M05 / 'May' sum format=NumFmt. ;
Define M06 / 'Jun' sum format=NumFmt. ;
Define M07 / 'Jul' sum format=NumFmt. ;
Define M08 / 'Aug' sum format=NumFmt. ;
Define M09 / 'Spt' sum format=NumFmt. ;
Define M10 / 'Oct' sum format=NumFmt. ;
Define M11 / 'Nov' sum format=NumFmt. ;
Define M12 / 'Dec' sum format=NumFmt. ;
Define YTD / 'YTD' sum format=NumFmt. ;
	Compute m01;
		if m01 lt 0 then
        call define( _ROW_, "style", "style={background=pink}" );
	Endcomp;
	Compute m02;
		if m02 lt 0 then
        call define( _ROW_, "style", "style={background=pink}" );
	Endcomp;
	Compute m03;
		if m03 lt 0 then
        call define( _ROW_, "style", "style={background=pink}" );
	Endcomp;
	Compute m04;
		if m04 lt 0 then
        call define( _ROW_, "style", "style={background=pink}" );
	Endcomp;
	Compute m05;
		if m05 lt 0 then
        call define( _ROW_, "style", "style={background=pink}" );
	Endcomp;
	Compute m06;
		if m06 lt 0 then
        call define( _ROW_, "style", "style={background=pink}" );
	Endcomp;
	Compute m07;
		if m07 lt 0 then
        call define( _ROW_, "style", "style={background=pink}" );
	Endcomp;
	Compute m08;
		if m08 lt 0 then
        call define( _ROW_, "style", "style={background=pink}" );
	Endcomp;
	Compute m09;
		if m09 lt 0 then
        call define( _ROW_, "style", "style={background=pink}" );
	Endcomp;
	Compute m10;
		if m10 lt 0 then
        call define( _ROW_, "style", "style={background=pink}" );
	Endcomp;
	Compute m11;
		if m11 lt 0 then
        call define( _ROW_, "style", "style={background=pink}" );
	Endcomp;
	Compute m12;
		if m12 lt 0 then
        call define( _ROW_, "style", "style={background=pink}" );
	Endcomp;		

run;
ods excel close;

[ballardw]

Thank you for providing a workable example data set.

Before going too far what do you thing the _row_ means in this statement?

call define( _ROW_, "style", "style={background=pink}" );

Please include the definitions of a custom format, such as your NUMFMT when included in code. It may be that might impact the solution.

If you want to highlight a cell based on the value in the cell the easiest is to use a custom format and a style override. A very reduced version as I am too lazy to basically repeat the same thing many times so just applied to one of the MO values.

proc format;
value pinkback
low-<0 ='pink'
;
run;

proc report data=have spanrows split='*'  style={BORDERCOLOR=DARK1 BORDERWIDTH=1}  style(column header)=[verticalalign=top];
	Columns Partner Year Source GroupS M01 ;
	Define Partner			/ 'Partner' Group;
	Define Year				/ 'Year' Group;
	Define Source			/ 'Source' Group;
	Define GroupS			/ 'GroupS' Group;
Define M01 / 'Jan' sum format=NumFmt. style={background=pinkback.} ;
run;

If you have the same coloring rule for multiple variables then they can share the same format.

The style override in effect looks at the value to display in the cell and then uses the formatted value as the setting for the specific cell attribute using the format.

Personally I would use a single numeric value for month, a custom format to display the abbreviation, and have that variable an Across variable. O

[Patrick]

If you just need to print your source data without any aggregation then below should work.

[Ksharp · Accepted solution]

Compute m01;
  if m01 lt 0 then
        call define( _ROW_, "style", "style={background=pink}" );
 Endcomp;

----->

 Compute m01;
  if m01.sum lt 0 then
        call define( _col_, "style", "style={background=pink}" );
 Endcomp;