Add Text to Date Column Summary

[rajesh1980]

Hi ,

I have a ODS Excel SAS PROC report which has Date column and i am group on that column as well. However towards the end i want to add the text 'TOTALS' to this date column summary. But since the column is formatted as DATE it is not displaying the text field. Any inputs on how to achieve this. 

This is how my program looks like 

proc report data=CUST_REV_MONTHLY nowd spanrows 
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]

;

	column pstng_dt contract lcontract billdocn stmt_amt_sum kwh_sum oper_amt_sum;

	define pstng_dt / f=mmddyy10. order=internal 'POSTING DATE' group style(column)=[vjust=middle just=left backgroundcolor=cx4C7DC4 fontweight=bold foreground=white];

	define contract / 'CONTRACT' group style(column)=[vjust=middle just=left backgroundcolor=cx4C7DC4 fontweight=bold foreground=white];
	define lcontract / 'LEGACY CONTRACT' group style(column)=[vjust=middle just=left backgroundcolor=cx4C7DC4 fontweight=bold foreground=white];
	define billdocn / 'BILL DOC NO' group style(column)=[vjust=middle just=left backgroundcolor=cx4C7DC4 fontweight=bold foreground=white];

	define stmt_amt_sum / 'STMT REV AMT' style(column)=[backgroundcolor = white tagattr='format:$#,##0.00;[Red]($#,##0.00);0.00;'] ;
	define kwh_sum / 'KWH USAGE' style(column)=[backgroundcolor = lightgrey];
	define oper_amt_sum / 'OPER REV AMT' style(column)=[backgroundcolor = cxABABAB tagattr='format:$#,##0.00;[Red]($#,##0.00);0.00;'] ;

	break after pstng_dt / summarize; /* Every time the pstng_dt Changes Summarize the table (SubTotal)*/

	compute after;
		pstng_dt = "TOTALS" ; /*When everything ends Add the literal 'Total' in the pstng_dt columns*/
	endcomp;

	rbreak after / summarize; /* Finally summarize the whole table (Grand Totals)*/

run;

This is what im getting

This is how i want it

Kindly Help

Regards..

Rajesh

[RichardDeVen · Accepted solution]

For the ODS EXCEL destination, in the COMPUTE AFTER block, you can use CALL DEFINE to place a formula in a cell via the STYLE=[TAGATTR= feature

  compute after;
    call define ('_c1_', 'style', 'style=[tagattr=''formula:="Totals"'']');
  endcomp;

Example:

data have;
  do date = '01-OCT2020'd to '04-OCT-2020'd;
    do contract = 1 to 3;
      legacy = contract + 1e6;
      do billdoc = 1 to 2;
        _n_ + 1;
        revenue_sum = 1000 + _n_ * 21;
        kw_sum = 100 + _n_ * 2;
        output;
      end;
    end;
  end;

 format revenue_sum dollar10.2 date mmddyy10.;
run;

ods excel file='report.xlsx';

proc report data=have
  spanrows 
  style(column)=[pretext="aaa "]
;
  columns date contract legacy billdoc revenue_sum kw_sum;
  define date / group;
  define contract / group;
  define legacy / group;
  define billdoc / group;
  
  break after date / summarize style=[background=lightgrey];
  rbreak after / summarize style=[fontsize=14pt];

  compute after;
    call define ('_c1_', 'style', 'style=[tagattr=''formula:="Totals"'']');
  endcomp;
run;

ods excel close;

Excel image

[rajesh1980]

Thank you very much sir!! It worked!