Need help formatting Excel file created with Proc Report, SAS 9.4M3

[Wolverine]

I posted about a similar topic a couple months ago, and I've made some progress since then.  But I'll be making a lot of these reports, so the more I can automate, the better.

I've attached an Excel file that shows what SAS actually produces, as well as a file that I had to manually edit to make it look like I want. Note that it's in landscape and I selected the option in Excel so that all columns fit on one page.  Also, there is a footer that includes the report number and the date it was produced.  And the column totals from Column B is copied to Column J, C is copied to K, etc.

I would appreciate any help with SAS syntax that would automate the above items, add borders, etc. 

options mprint;

%macro freqs (state,statename);

LIBNAME base "D:\&state. only\base";
LIBNAME &state "D:\&state. only";


%do year=2005 %to 2012;

%macro by_ds (ds);

DATA &state..&ds.&year._&state._TEMP (compress = yes); SET base.&ds.&year._&state.;

year=input("&year",8.);

DOByr1=year-0; 
DOByr2=year-18;

DOByr1c=put(DOByr1,$4.);
DOByr2c=put(DOByr2,$4.);


/**Delete cases that do not meet age requirements**/
IF EL_DOB > DOByr1c||"1231"  
     OR EL_DOB < DOByr2c||"0101" 
     THEN delete;


%mend;

%by_ds (IP)
%by_ds (OT)

ODS TRACE ON;
DATA &state..IP&year._&state._bdayCUT1 (compress = yes); SET &state..IP&year._&state._TEMP;

LENGTH ADJUST_CD_char $63;

IF ADJUST_CD = 0 THEN ADJUST_CD_char = "0-NO ADJUSTMENT OF CLAIMS WAS REQUIRED";
IF ADJUST_CD = 1 THEN ADJUST_CD_char = "1-AT LEAST ONE ORIGINAL CLAIM AND AT LEAST ONE ADJUSTMENT CLAIM";
IF ADJUST_CD = 2 THEN ADJUST_CD_char = "2-NOT POSSIBLE TO COMPLETE THE ADJUSTMENT PROCESS";


title "&state.&year. 2005-2012 IP Adjustment Code summary";  
PROC FREQ NOPRINT; 
	TABLES ADJUST_CD_char  /OUT=&state..IP&year._&state.ADJUST_CD_FREQ; 

ODS OUTPUT OneWayFreqs(match_all)=freqs;
RUN;
ODS TRACE OFF;

DATA &state..IP&year._&state._FREQ_ADJUST_CD; SET &state..IP&year._&state.ADJUST_CD_FREQ;

&state.&year._IPcount = COUNT;
&state.&year._IPpercent = PERCENT/100;

DROP COUNT PERCENT;

RUN;



ODS TRACE ON;
DATA &state..OT&year._&state._bdayCUT1 (compress = yes); SET &state..OT&year._&state._TEMP;


LENGTH ADJUST_CD_char $63;

IF ADJUST_CD = 0 THEN ADJUST_CD_char = "0-NO ADJUSTMENT OF CLAIMS WAS REQUIRED";
IF ADJUST_CD = 1 THEN ADJUST_CD_char = "1-AT LEAST ONE ORIGINAL CLAIM AND AT LEAST ONE ADJUSTMENT CLAIM";
IF ADJUST_CD = 2 THEN ADJUST_CD_char = "2-NOT POSSIBLE TO COMPLETE THE ADJUSTMENT PROCESS";

title "&state.&year. OT Type Claim Code summary";  
PROC FREQ NOPRINT; 
	TABLES ADJUST_CD_char  /OUT=&state..OT&year._&state.ADJUST_CD_FREQ; 

ODS OUTPUT OneWayFreqs(match_all)=freqs;
RUN;
ODS TRACE OFF;

DATA &state..OT&year._&state._FREQ_ADJUST_CD; SET &state..OT&year._&state.ADJUST_CD_FREQ;

&state.&year._OTcount = COUNT;
&state.&year._OTpercent = PERCENT/100;

DROP COUNT PERCENT;

RUN;



%end;
/*This %end statement runs all the years before merging them*/

DATA &state..IPOT_&state._ADJUST_CD_MERGE; MERGE &state..IP2005_&state._FREQ_ADJUST_CD  &state..IP2006_&state._FREQ_ADJUST_CD
	 &state..IP2007_&state._FREQ_ADJUST_CD &state..IP2008_&state._FREQ_ADJUST_CD &state..IP2009_&state._FREQ_ADJUST_CD
	 &state..IP2010_&state._FREQ_ADJUST_CD &state..IP2011_&state._FREQ_ADJUST_CD &state..IP2012_&state._FREQ_ADJUST_CD 
	 
	 &state..OT2005_&state._FREQ_ADJUST_CD &state..OT2006_&state._FREQ_ADJUST_CD
	 &state..OT2007_&state._FREQ_ADJUST_CD &state..OT2008_&state._FREQ_ADJUST_CD  &state..OT2009_&state._FREQ_ADJUST_CD
	 &state..OT2010_&state._FREQ_ADJUST_CD &state..OT2011_&state._FREQ_ADJUST_CD &state..OT2012_&state._FREQ_ADJUST_CD;
	BY ADJUST_CD_char;
	
RUN;	


ods excel file="Z:\MAX data\SYNTAX\!prcdr rev\SUMMARIES\&state. 2.2.2,2.3.2 - ADJUST_CD (1).xlsx" 
 /* will apply an appearance style */
 style=Analysis
 options(
  /* for multiple procs/sheet */
  sheet_interval="none" 
  /* name the sheet tab */
  sheet_name="IPOT PRCDR"
  embedded_titles = 'yes'
  absolute_column_width = "28,8.71,8.71,8.71,8.71,8.71,8.71,8.71,8.71,8.71,8.71,8.71,8.71,8.71,8.71,8.71,8.71,8.71,8.71,9.43,9.43,9.43,9.43,9.43,9.43,9.43,9.43,9.43,9.43,9.43,9.43,9.43,9.43,9.43,9.43"
  /*auto_subtotals = 'yes' --THIS OPTION DOESN'T WORK WITH THIS TAGSET*/
  hidden_columns = "2,3,4,5,6,7,8,9,18,19,20,21,22,23,24,25"
 );
 
/* add some formatted text */
ods escapechar='~';
/*ods text="~S={font_size=14pt font_weight=bold}~ &state. 2005-2010 OT Proc code summary";*/


RUN;



proc report data=&state..IPOT_&state._ADJUST_CD_MERGE spanrows headline missing SPLIT='_';

title1 "Adjustment code claims 2.2.2, 2.3.2";  
title2 "&statename., 0-18 years old";  
title3 "2005-2012";  


	COLUMN ADJUST_CD_char 
	
	&state.2005_IPcount &state.2006_IPcount &state.2007_IPcount &state.2008_IPcount &state.2009_IPcount 
	&state.2010_IPcount &state.2011_IPcount &state.2012_IPcount 
		
	&state.2005_IPpercent &state.2006_IPpercent &state.2007_IPpercent &state.2008_IPpercent &state.2009_IPpercent 
	&state.2010_IPpercent &state.2011_IPpercent &state.2012_IPpercent 
		
	&state.2005_OTcount &state.2006_OTcount &state.2007_OTcount &state.2008_OTcount &state.2009_OTcount 
	&state.2010_OTcount &state.2011_OTcount &state.2012_OTcount 
		
	&state.2005_OTpercent &state.2006_OTpercent &state.2007_OTpercent &state.2008_OTpercent &state.2009_OTpercent 
	&state.2010_OTpercent &state.2011_OTpercent &state.2012_OTpercent;
		
	define ADJUST_CD_char / group;
	define &state.2005_IPcount / group style(column)={tagattr='format:#,###'} DISPLAY ANALYSIS SUM;
	define &state.2006_IPcount / group style(column)={tagattr='format:#,###'} DISPLAY ANALYSIS SUM;
	define &state.2007_IPcount / group style(column)={tagattr='format:#,###'} DISPLAY ANALYSIS SUM;
	define &state.2008_IPcount / group style(column)={tagattr='format:#,###'} DISPLAY ANALYSIS SUM;
	define &state.2009_IPcount / group style(column)={tagattr='format:#,###'} DISPLAY ANALYSIS SUM;
	define &state.2010_IPcount / group style(column)={tagattr='format:#,###'} DISPLAY ANALYSIS SUM;
	define &state.2011_IPcount / group style(column)={tagattr='format:#,###'} DISPLAY ANALYSIS SUM;
	define &state.2012_IPcount / group style(column)={tagattr='format:#,###'} DISPLAY ANALYSIS SUM;
	
	define &state.2005_IPpercent / group style(column)={tagattr='format:0.0%'};
	define &state.2006_IPpercent / group style(column)={tagattr='format:0.0%'};
	define &state.2007_IPpercent / group style(column)={tagattr='format:0.0%'};
	define &state.2008_IPpercent / group style(column)={tagattr='format:0.0%'};
	define &state.2009_IPpercent / group style(column)={tagattr='format:0.0%'};
	define &state.2010_IPpercent / group style(column)={tagattr='format:0.0%'};
	define &state.2011_IPpercent / group style(column)={tagattr='format:0.0%'};
	define &state.2012_IPpercent / group style(column)={tagattr='format:0.0%'};	
	
	define &state.2005_OTcount / group style(column)={tagattr='format:#,###'} DISPLAY ANALYSIS SUM;
	define &state.2006_OTcount / group style(column)={tagattr='format:#,###'} DISPLAY ANALYSIS SUM;
	define &state.2007_OTcount / group style(column)={tagattr='format:#,###'} DISPLAY ANALYSIS SUM;
	define &state.2008_OTcount / group style(column)={tagattr='format:#,###'} DISPLAY ANALYSIS SUM;
	define &state.2009_OTcount / group style(column)={tagattr='format:#,###'} DISPLAY ANALYSIS SUM;
	define &state.2010_OTcount / group style(column)={tagattr='format:#,###'} DISPLAY ANALYSIS SUM;
	define &state.2011_OTcount / group style(column)={tagattr='format:#,###'} DISPLAY ANALYSIS SUM;
	define &state.2012_OTcount / group style(column)={tagattr='format:#,###'} DISPLAY ANALYSIS SUM;
	
	define &state.2005_OTpercent / group style(column)={tagattr='format:0.0%'};
	define &state.2006_OTpercent / group style(column)={tagattr='format:0.0%'};
	define &state.2007_OTpercent / group style(column)={tagattr='format:0.0%'};
	define &state.2008_OTpercent / group style(column)={tagattr='format:0.0%'};
	define &state.2009_OTpercent / group style(column)={tagattr='format:0.0%'};
	define &state.2010_OTpercent / group style(column)={tagattr='format:0.0%'};
	define &state.2011_OTpercent / group style(column)={tagattr='format:0.0%'};
	define &state.2012_OTpercent / group style(column)={tagattr='format:0.0%'};
	
/*compute after;
	PRCDR_CD_SYS="Column N";
endcomp;*/


/*compute &state.2005_PERCENT;
	if upcase(_break_)='_RBREAK_' then do;
	call define(_col_,"style","style={tagattr=""formula:= B8""}");*/
	/*call define(_col_,"style","style={tagattr=""formula:RC[-5]""}");*/
	/*call define('PRCDR_CD_SYS',"style","style={tagattr=""formula:= sum($D$4:$D9)""}");
	call define('&state.2006_PERCENT',"style","style={tagattr=""formula:= sum($D$4:$D9)""}");*/

/*end;
endcomp;*/

compute after;
	ADJUST_CD_char="N Column Total";
endcomp;

rbreak after / summarize;

RUN;

%mend;

/*%freqs(FL,Florida)
%freqs(IL,Illinois)*/
/*%freqs(LA,Louisiana)*/
/*%freqs(MI,Michigan)*/
/*%freqs(NY, New York)*/

/*%freqs(SC,South Carolina)*/

/*%freqs(TX,Texas)*/
%freqs(UT,Utah)

/*ODS HTML close;*/

ods excel close;

[Wolverine]

I forgot to mention that when I run multiple states, only the first state is included in the Excel file.

[ballardw]

---

@Wolverine wrote:

I forgot to mention that when I run multiple states, only the first state is included in the Excel file.

---

Do you have a variable for the state? If so sort by that variable and use BY statevariable; to generate an output table for each level.

If not you'll have to tell us how we know when the state changes.

[Reeza]

Asking someone to wade through that much code is probably a bit unreasonable for a forum posting. 

Can you simplify your question a bit  so it's more appropriate for here? A good way is to determine the feature you're looking for and seeing how you can get that built using proc report and a dataset from SASHELP. If we can't run the code then it's more difficult to help and debug. Yes, creating that is a lot of work but you're asking for a lot as well.

Here's an index of the tagset options and the corresponding pages where you can find information/examples of how to implement the feature.

https://vasug.files.wordpress.com/2011/07/excelxppaperindex.pdf

[lakshmi_74]

I am giving some clues, You can follow this :

data have;
input adj_code year value treatment $ state $;
datalines;
0 2005 12 therap MI
1 2006 20 inpati CA
2 2007 22 inpati MI
1 2005 21 inpati MI
2 2005 22 therap CA
0 2005 10 therap CA
1 2006 19 inpati CA
2 2007 10 inpati MI
2 2006 20 therap CA
1 2006 11 inpati MI
0 2005 90 therap CA
0 2006 11 inpati MI
0 2005 99 therap MI
;
run;
proc format;
value adj_code
0="0-NO ADJUSTMENT OF CLAIMS WAS REQUIRED"
1="1-AT LEAST ONE ORIGINAL CLAIM AND AT LEAST ONE ADJUSTMENT CLAIM"
2="2-NOT POSSIBLE TO COMPLETE THE ADJUSTMENT PROCESS";
value $treatment
'therap'="OTHER THERAPY PERCENTAGES"
'inpati'="INPATIENT PERCENTAGES";
run;
ods escapechar='^';
ods pdf;
proc report nowd data=have spanrows;
title1 "Adjustment code claims 2.2.2, 2.3.2";
title2 "&statename., 0-18 years old";
title3 "2005-2007";
column state (adj_code) treatment,year,value;* year value treatment;
define state/group;
break after state/page;
define adj_code/'Adjustment Code' group format=adj_code.;
define treatment/''across order=internal format=$treatment.;
define year/' 'across order=internal ;
define value/'' sum;
rbreak after/ summarize;
run;
ods pdf close;