Conditional Formatting of Columns in ODS Excel Report

rajesh1980 — Wed, 09 Dec 2020 08:05:34 GMT

Hi,

I am creating a ODS Excel PROC REPORT where i have a attribute column which is displayed across along with a metric column to display the metric. The attribute column has revenue figures as well as other KPIs. The revenue figures i want them to be formatted with a $ symbol and the other KPIs as normal.

This is the code i have , the CHARGE_TEXT field is the KPI column and CHARGE is metric column.

Im using a COMPUTE CHARGE_TEXT block to try and add a conditional tagattr, but it is not working.i am attaching the code and output.KIndly help with my ask.

proc report data=BASE_UNBUND_REV_ELEC_12M_MOD nowd spanrows missing
style(report)=[bordercolor=black borderwidth=3pt]
style(header)=[verticalalign=middle backgroundcolor=cx4C7DC4 foreground=white]
style(summary)=[backgroundcolor=black foreground=white fontstyle=roman fontweight=bold bordercolor=black borderwidth=2pt]
;
	by pucgrp;
	
	column pucgrp ratelit ('Rate Literal' ratelit_disp) consumption_dt ('Year' consumption_yr_disp)  ('Month' consumption_dt_disp) (charge_text, (charge));

	define pucgrp / '' group noprint;

	define ratelit / '' group noprint;
	define ratelit_disp / '' computed style(column)=[backgroundcolor=lightgrey];

	define consumption_dt / order=internal group '' noprint;
	define consumption_yr_disp / f=year4. '' computed style(column)=[backgroundcolor=lightgrey];
	define consumption_dt_disp / f=monname3. '' computed style(column)=[backgroundcolor=lightgrey];

	define charge_text / across order=data '' style(column)=[backgroundcolor = white width=40%] ;
	define charge / '' style(column)=[backgroundcolor = white] ;
	
	compute before ratelit;
		hold_ratelit = ratelit;
	endcomp;

	compute before consumption_dt;
		hold_consumption_dt = consumption_dt;
	endcomp;


	compute ratelit_disp / char length=25;
		ratelit_disp = hold_ratelit;
	endcomp;

	compute consumption_yr_disp;
		consumption_yr_disp = hold_consumption_dt; 
	endcomp;

	compute consumption_dt_disp;
		consumption_dt_disp = hold_consumption_dt; 
	endcomp;
	
	compute charge_text;
		if charge_text = 'Billed Months' then 
			do;
				call define('charge','style','style={tagattr="0.00,[Red](0.00),0.00"}');
			end;
		else;
			do;
				call define('charge','style','style={tagattr="$0.00,[Red]($0.00),$0.00"}');
			end;
	endcomp;
	
	compute after ratelit;
		call define ('consumption_yr_disp', 'style', 'style=[tagattr=''formula:=""'']');
		call define ('consumption_dt_disp', 'style', 'style=[tagattr=''formula:=""'']');
	endcomp;
	break after ratelit / summarize; /* Every time the ratelit Changes Summarize the table (SubTotal)*/

run;

This is output im getting. Wherever i have pointed out with the arrow i need to be displayed as style={tagattr="0.00,[Red](0.00),0.00"} and all others as style={tagattr="$0.00,[Red]($0.00),$0.00"}

this is how my data looks

PUCGRP	RATELIT	CONSUMPTION_DT	ID	CHARGE_TEXT	CHARGE
Domestic	D-CARE-SDP-O/BUND	01Jul2020	0	Billed Months	1.50
Domestic	D-CARE-SDP-O/BUND	01Jul2020	0	Billed KWH	5013.97
Domestic	D-CARE-SDP-O/BUND	01Jul2020	0	Billed Operating Revenue	777.39
Domestic	D-CARE-SDP-O/BUND	01Jul2020	1	Generation (Post)	506.27
Domestic	D-CARE-SDP-O/BUND	01Jul2020	2	GTSR Charge	0.00
Domestic	D-CARE-SDP-O/BUND	01Jul2020	3	GTSR Credit	0.00
Domestic	D-CARE-SDP-O/BUND	01Jul2020	4	GTSR Indifference Adj	0.00
Domestic	D-CARE-SDP-O/BUND	01Jul2020	5	PTR/SEP Fixed Capacity Cr-G	0.00
Domestic	D-CARE-SDP-O/BUND	01Aug2020	0	Billed Months	1.23
Domestic	D-CARE-SDP-O/BUND	01Aug2020	0	Billed KWH	2382.29
Domestic	D-CARE-SDP-O/BUND	01Aug2020	0	Billed Operating Revenue	358.22
Domestic	D-CARE-SDP-O/BUND	01Aug2020	1	Generation (Post)	334.46
Domestic	D-CARE-SDP-O/BUND	01Aug2020	2	GTSR Charge	0.00
Domestic	D-CARE-SDP-O/BUND	01Aug2020	3	GTSR Credit	0.00
Domestic	D-CARE-SDP-O/BUND	01Aug2020	4	GTSR Indifference Adj	0.00
Domestic	D-CARE-SDP-O/BUND	01Aug2020	5	PTR/SEP Fixed Capacity Cr-G	0.00
Domestic	D-CARE-SDP-O/BUND	01Sep2020	0	Billed Months	1.77
Domestic	D-CARE-SDP-O/BUND	01Sep2020	0	Billed KWH	1108.08
Domestic	D-CARE-SDP-O/BUND	01Sep2020	0	Billed Operating Revenue	119.81
Domestic	D-CARE-SDP-O/BUND	01Sep2020	1	Generation (Post)	143.42
Domestic	D-CARE-SDP-O/BUND	01Sep2020	2	GTSR Charge	0.00
Domestic	D-CARE-SDP-O/BUND	01Sep2020	3	GTSR Credit	0.00
Domestic	D-CARE-SDP-O/BUND	01Sep2020	4	GTSR Indifference Adj	0.00
Domestic	D-CARE-SDP-O/BUND	01Sep2020	5	PTR/SEP Fixed Capacity Cr-G	0.00
Domestic	D-CARE-SDP-O/BUND	01Oct2020	0	Billed Months	0.50
Domestic	D-CARE-SDP-O/BUND	01Oct2020	0	Billed KWH	276.67
Domestic	D-CARE-SDP-O/BUND	01Oct2020	0	Billed Operating Revenue	28.63
Domestic	D-CARE-SDP-O/BUND	01Oct2020	1	Generation (Post)	24.71
Domestic	D-CARE-SDP-O/BUND	01Oct2020	2	GTSR Charge	0.00
Domestic	D-CARE-SDP-O/BUND	01Oct2020	3	GTSR Credit	0.00
Domestic	D-CARE-SDP-O/BUND	01Oct2020	4	GTSR Indifference Adj	0.00
Domestic	D-CARE-SDP-O/BUND	01Oct2020	5	PTR/SEP Fixed Capacity Cr-G	0.00

Re: Conditional Formatting of Columns in ODS Excel Report

Cynthia_sas — Wed, 09 Dec 2020 18:46:53 GMT

Hi:
With ACROSS items and especially when you have nested a numeric value under the ACROSS item, you cannot change anything in a CALL DEFINE by using the variable name, as you show. You must either use absolute column numbers, as explained in this paper: https://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf

Look at the example specifically that starts on page 9 and the CALL DEFINE example that is on page 11.

Cynthia

Re: Conditional Formatting of Columns in ODS Excel Report

rajesh1980 — Wed, 09 Dec 2020 19:28:46 GMT

@Cynthia_sas Thank you Ma'am. It worked!

I used

	compute charge_text;
		call define('_c7_','style','style={tagattr="0.00,[Red](0.00),0"}');
		call define('_c8_','style','style={tagattr="0.00,[Red](0.00),0"}');
	endcomp;

One thing i learned today is that the column numbers are based on the definition in the column statement. I was trying c4 and c5 it wasnt working then by trial and error i found out it was c7 and c8

that is because the column statement is defined as

	column pucgrp ratelit ('Rate Literal' ratelit_disp) consumption_dt ('Year' consumption_yr_disp)  ('Month' consumption_dt_disp) (charge_text, (charge));

so charge_text is C7.

topic Conditional Formatting of Columns in ODS Excel Report in ODS and Base Reporting

Conditional Formatting of Columns in ODS Excel Report

Re: Conditional Formatting of Columns in ODS Excel Report

Re: Conditional Formatting of Columns in ODS Excel Report