topic Re: Add Text to Date Column Summary in ODS and Base Reporting

Add Text to Date Column Summary

rajesh1980 — Tue, 13 Oct 2020 08:52:58 GMT

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

Re: Add Text to Date Column Summary

RichardDeVen — Tue, 13 Oct 2020 12:38:39 GMT

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

Re: Add Text to Date Column Summary

rajesh1980 — Thu, 15 Oct 2020 19:17:20 GMT

Thank you very much sir!! It worked!