Trying to generate a summary report with ODS Excel in SAS 9.4M3

[Wolverine]

I'm trying to generate an Excel summary report that looks pretty much like the file I attached. I'll be generating these reports for several states, so I'm trying to make them compact so it's easy to compare results across states.  Note that Columns C thru H are hidden.  The hidden columns contain frequencies for each year (C=2005, D=2006, etc), and these are used to generate the Total N for each year.

I'm not sure what procedure I should be using.  Proc Print is working well for me, except that I can't get it to add the totals at the end.  I also found an example using Proc Report, but the resulting Excel file isn't as close to what I want.  Many of the options in the Proc Report code don't work in Proc Print and vice versa.  The code below includes both approaches.

LIBNAME UT "D:\UT only";

proc format;
  VALUE $proc_sys_label
  
"01" = "01-CPT-4 (HCPCS LEVEL 1)"
"02" = "02-ICD-9-CM"
"06" = "06-HCPCS (HCPCS LEVELS 2 AND 3)"
"07" = "07-ICD-10 (FUTURE USE)"
"10" = "10-OTHER SYSTEMS"
"11" = "11-OTHER SYSTEMS"
"12" = "12-OTHER SYSTEMS"
"13" = "13-OTHER SYSTEMS"
"14" = "14-OTHER SYSTEMS"
"15" = "15-OTHER SYSTEMS"
"16" = "16-OTHER SYSTEMS"
"17" = "17-OTHER SYSTEMS"
"18" = "18-OTHER SYSTEMS"
"88" = "88-NOT APPLICABLE"
"99" = "99-UNKNOWN"
"" = "99-UNKNOWN";


ods excel file="Z:\MAX data\SYNTAX\!prcdr rev\OUTPUT\UT 2005-2010 IP, OT PRCDR REV freqs (4B TEST output data file).xlsx" 
 /* will apply an appearance style */
 style=Analysis
 options(
  /* for multiple procs/sheet */
  sheet_interval="none" 
  /* name the sheet tab */
  sheet_name="OT PRCDR"
  embedded_titles = 'yes'
  absolute_column_width = "3,23,8.57,8.57,8.57,8.57,8.57,8.57,8.57,8.57,8.57,8.57,8.57,8.57"
  /*auto_subtotals = 'yes' --THIS OPTION DOESN'T WORK WITH THIS TAGSET*/
  hidden_columns = "3,4,5,6,7,8" /*"C,D,E,F,G,H"*/
 );
 
/* add some formatted text */
ods escapechar='~';
/*ods text="~S={font_size=14pt font_weight=bold}~ UT 2005-2010 OT Proc code summary";*/


	
PROC PRINT DATA = UT.OT_UT_FREQ_MERGE;

label UT2005_PERCENT = 'UT2005*PERCENT'
	UT2006_PERCENT = 'UT2006*PERCENT'
	UT2007_PERCENT = 'UT2007*PERCENT'
	UT2008_PERCENT = 'UT2008*PERCENT'
	UT2009_PERCENT = 'UT2009*PERCENT'
	UT2010_PERCENT = 'UT2010*PERCENT';
	

var PRCDR_CD_SYS UT2005_COUNT UT2006_COUNT UT2007_COUNT UT2008_COUNT UT2009_COUNT 
	UT2010_COUNT;
var UT2005_PERCENT UT2006_PERCENT UT2007_PERCENT UT2008_PERCENT
	UT2009_PERCENT UT2010_PERCENT / style(data)={tagattr='format:0.00%'};
/* formula:SUM(R3C3:R10C8*/



RUN;



proc report data=UT.OT_UT_FREQ_MERGE spanrows;
	COLUMN Obs PRCDR_CD_SYS UT2005_COUNT UT2006_COUNT UT2007_COUNT UT2008_COUNT UT2009_COUNT 
		UT2010_COUNT UT2005_PERCENT UT2006_PERCENT UT2007_PERCENT UT2008_PERCENT 
		UT2009_PERCENT UT2010_PERCENT;


	define PRCDR_CD_SYS / group;
	define UT2005_COUNT / group;
	define UT2006_COUNT / group;
	define UT2007_COUNT / group;
	define UT2008_COUNT / group;
	define UT2009_COUNT / group;
	define UT2010_COUNT / group;
	define UT2005_PERCENT / group;
	define UT2006_PERCENT / group;
	define UT2007_PERCENT / group;
	define UT2008_PERCENT / group;
	define UT2009_PERCENT / group;
	define UT2010_PERCENT / group;


compute after;
	PRCDR_CD_SYS="Total N";
endcomp;


compute UT2005_COUNT;
	if upcase(_break_)='_RBREAK_' then do;
	call define(_col_,"style","style={tagattr=""formula:= sum($C$4:$C9""}");
end;
endcomp;
rbreak after / summarize;



RUN;

ods excel close;

[Wolverine]

I did some more investigating, and I discovered that Proc Report is better suited to my needs. In fact, I can use Proc Report to generate the column totals before I even dump the output into Excel.

I also found that many people have trouble with Excel cell naming conventions (ie, A1, A2, etc) than absolute reference (RC[-1]).

So here is my updated syntax.  It works, except that I need to move the 200x_COUNT total to the bottom of the 200x_PERCENTAGE column.  That's what the "formula:RC[-5]" part is intended to do -- copy the cell from 5 columns over.  But it's not working.

proc report data=UT.OT_UT_FREQ_MERGE spanrows;

title "PRCDR Proc Report";  

	COLUMN PRCDR_CD_SYS UT2005_COUNT UT2006_COUNT UT2007_COUNT UT2008_COUNT UT2009_COUNT 
		UT2010_COUNT UT2005_PERCENT UT2006_PERCENT UT2007_PERCENT UT2008_PERCENT 
		UT2009_PERCENT UT2010_PERCENT;
		

	define PRCDR_CD_SYS / group;
	define UT2005_COUNT / group DISPLAY ANALYSIS SUM;
	define UT2006_COUNT / group DISPLAY ANALYSIS SUM;
	define UT2007_COUNT / group DISPLAY ANALYSIS SUM;
	define UT2008_COUNT / group DISPLAY ANALYSIS SUM;
	define UT2009_COUNT / group DISPLAY ANALYSIS SUM;
	define UT2010_COUNT / group DISPLAY ANALYSIS SUM;
	define UT2005_PERCENT / group style(column)={tagattr='format:0.00%'};
	define UT2006_PERCENT / group style(column)={tagattr='format:0.00%'};
	define UT2007_PERCENT / group style(column)={tagattr='format:0.00%'};
	define UT2008_PERCENT / group style(column)={tagattr='format:0.00%'};
	define UT2009_PERCENT / group style(column)={tagattr='format:0.00%'};
	define UT2010_PERCENT / group style(column)={tagattr='format:0.00%'};


compute after;
	PRCDR_CD_SYS="Column N";
endcomp;


compute UT2005_PERCENT;
	if upcase(_break_)='_RBREAK_' then do;
	call define(_col_,"style","style={tagattr=""formula:RC[-5]""}");


end;
endcomp;
rbreak after / summarize;



RUN;