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 |
@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.
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.