Proc Report to Excel - Header background issue

[cuevasj]

I have three issues with a report that I am generating. 

1) I have some 'spanned' columns with heading text that I need highlighted, but when I use what I have been able to find on the web, I can only highlight the text, and not the cell. (look for the following code in the full text code below: 

("^S={backgroundcolor=#DDEBF7}2017-18 Actual DOE School Grade"
doe_ela doe_ela_gain doe_ela_bq 
doe_mth doe_mth_gain doe_mth_bq 
doe_sci doe_scs 
doe_accel_ms doe_grad_rate doe_accel_hs 
doe_points doe_components doe_per_points doe_grade)

2.) the highlighting, such as it is, does not translate into the excel file itself. It will highlight the text in the "Results - SAS Report" , but not on the produced excel file.

3.) I have the top two rows "frozen", but when I open the excel file and attempt to print the file the top most row disappears for pp 2-4.

(I will attach some pictures to show the difference in (1) and (2) on report and excel file.)

Here is the full code:

%global school_pk; 
%global prediction_type;
%let school_pk=9999;
%let prediction_type=Q1;

data _null_; 
t=today(); 
today=put(t,yymmddn8.); 
call symput('today',today); 
call symput('school_number',put(&school_pk,z4.)); 
run;

%let school_number=&school_number; 
%put &school_number;

%let school_year=&FocusSchoolYear;
%put &school_year;

data details_PY(keep=doe_year school_number school_name school_number_name 
doe_ela doe_ela_gain doe_ela_bq 
doe_mth doe_mth_gain doe_mth_bq 
doe_sci doe_scs 
doe_accel_ms doe_grad_rate doe_accel_hs 
doe_points doe_components doe_per_points doe_grade doe_grade_seq);
set SASdev.DOE_School_Grades;
where district_number='16'
and school_year=&FocusSchoolYear-1;
school_number_name=strip(substr(strip(school_number)||' '||propcase(strip(school_name)),1,43));
doe_ela=ela;
doe_ela_gain=ela_gains;
doe_ela_bq=ela_gains_bq;
doe_mth=math;
doe_mth_gain=math_gains;
doe_mth_bq=math_gains_bq;
doe_sci=science;
doe_scs=social_studies;
doe_accel_ms=ms_acceleration;
doe_grad_rate=grad_rate_PY;
doe_accel_hs=college_accel;
doe_points=total_points;
doe_components=total_components;
doe_per_points=percent_points;
doe_grade=school_grade;

if doe_grade='A' then doe_grade_seq=5; else
if doe_grade='B' then doe_grade_seq=4; else
if doe_grade='C' then doe_grade_seq=3; else
if doe_grade='D' then doe_grade_seq=2; else doe_grade_seq=1;

run;
proc sql;
create table details_PY as
select distinct
l.regional_sup_name as region_sup
, l.executive_director_name as xo
, l.board_member_name as board_member
, d.*
, case l.regional_sup_name
when 'Green, W.' then 1
when 'Cagle, S.' then 2
when 'Henry, M.' then 3
when 'Wright, C.' then 4
when 'Charter' then 6
else 99
end as sort
from
details_PY d
inner join whse.school_dim l on l.school_number=d.school_number and l.current_ind=1
order by
sort,2,3,school_number
;
quit;

data details_CY(keep=doe_year school_number school_name school_number_name prediction_type 
pred_ela_achv pred_ela_gain pred_ela_bq 
pred_mth_achv pred_mth_gain pred_mth_bq 
pred_sci_achv 
pred_scs_achv 
pred_accel_ms pred_grad_rate pred_accel_hs 
points components per_points grade grade_seq);
format prediction_type $char7.;
set LPM.School_Grade_Simulator;
where prediction_type="&prediction_type"
and ela_pred_prof_num ne .;
doe_year='2018-19';
school_number_name=strip(substr(strip(school_number)||' '||propcase(strip(school_name)),1,43));

pred_ela_achv = max(round((ela_pred_prof_num/ela_pred_prof_den)*100,1),0);
pred_ela_gain = max(round((ela_pred_gain_num/ela_pred_gain_den)*100,1),0);
pred_ela_bq = max(round((ela_pred_gain_bq_num/ela_pred_gain_bq_den)*100,1),0);
pred_mth_achv = max(round((mth_pred_prof_num/mth_pred_prof_den)*100,1),0);
pred_mth_gain = max(round((mth_pred_gain_num/mth_pred_gain_den)*100,1),0);
pred_mth_bq = max(round((mth_pred_gain_bq_num/mth_pred_gain_bq_den)*100,1),0);
pred_sci_achv = max(round((sci_pred_prof_num/sci_pred_prof_den)*100,1),0);
pred_scs_achv = max(round((scs_pred_prof_num/scs_pred_prof_den)*100,1),0);
pred_accel_ms = max(round(pred_ms_accel*100,1),0);
pred_grad_rate= max(round(pred_grad_rate*100,1),0);
pred_accel_hs = max(round(pred_hs_accel*100,1),0);

points=max(pred_ela_achv,0)
+ max(pred_ela_gain,0)
+ max(pred_ela_bq,0)
+ max(pred_mth_achv,0)
+ max(pred_mth_gain,0)
+ max(pred_mth_bq,0)
+ max(pred_sci_achv,0)
+ max(pred_scs_achv,0)
+ max(pred_accel_ms,0)
+ max(pred_grad_rate,0)
+ max(pred_accel_hs,0);

if max(ela_pred_prof_num,0) >1 then c01=1; else c01=0; 
if max(ela_pred_gain_num,0) >1 then c02=1; else c02=0;
if max(ela_pred_gain_bq_num,0)>1 then c03=1; else c03=0;
if max(mth_pred_prof_num,0) >1 then c04=1; else c04=0;
if max(mth_pred_gain_num,0) >1 then c05=1; else c05=0;
if max(mth_pred_gain_bq_num,0)>1 then c06=1; else c06=0;
if max(sci_pred_prof_num,0) >1 then c07=1; else c07=0;
if max(scs_pred_prof_num,0) >1 then c08=1; else c08=0;
if max(pred_ms_accel,0) >.1 then c09=1; else c09=0;
if max(pred_grad_rate,0) >.1 then c10=1; else c10=0;
if max(pred_hs_accel,0) >.1 then c11=1; else c11=0;

components=c01+c02+c03+c04+c05+c06+c07+c08+c09+c10+c11;

per_points=round(points/components,1);

if per_points ge 62 then grade='A'; else
if per_points ge 54 then grade='B'; else
if per_points ge 41 then grade='C'; else
if per_points ge 32 then grade='D'; else grade='F';

if grade='A' then grade_seq=5; else
if grade='B' then grade_seq=4; else
if grade='C' then grade_seq=3; else
if grade='D' then grade_seq=2; else grade_seq=1;

if prediction_type='BY' then prediction_seq=1; else 
if prediction_type='Q1' then prediction_seq=2; else 
if prediction_type='MY' then prediction_seq=3; else 
if preciction_type='Q3' then prediction_seq=4; else seq=5;

run; 
proc sql;
create table details_CY as
select distinct
l.regional_sup_name as region_sup
, l.executive_director_name as xo
, l.board_member_name as board_member
, d.*
, case l.regional_sup_name
when 'Green, W.' then 1
when 'Cagle, S.' then 2
when 'Henry, M.' then 3
when 'Wright, C.' then 4
when 'Charter' then 6
else 9
end as sort
from
details_CY d
inner join whse.school_dim l on l.school_number=d.school_number and l.current_ind=1
order by
sort,2,3,school_number
;
quit;

data details;
format prediction_type $char7.;
merge details_CY
details_PY;
by sort xo board_member school_number;
if grade='F' then grade_bkgd= 0; else
if grade_seq > doe_grade_seq then grade_bkgd= 1; else
if grade_seq < doe_grade_seq then grade_bkgd=-1; else
grade_bkgd= 0;
spacer1=' ';
spacer2=' ';
run;


ods escapechar='^';
ods tagsets.excelxp 
file="\\t002pmid03\sasdata\School Grades Simulator\All Components\Reports\Superintendent_SGP_Q1.xml" 
options(autofit_height='yes'
frozen_headers='2'
gridlines='yes'
orientation='landscape'
pages_fitwidth='1'
pages_fitheight='4'
row_repeat='2'
sheet_name='School Grade Simulator-MY'
/*wraptext='no'*/); 

options missing=' ' nomprint nomlogic;

proc report data=details missing nowd split='~' spanrows 
style(column)={font_face=Calibri font_size=10pt background=white borderwidth=1px}
style(header)={font_face=Calibri font_size=10pt background=white borderwidth=1px just=c font_weight=bold}; 
columns ( sort
(region_sup xo board_member school_number_name)

("^S={backgroundcolor=#DDEBF7}2017-18 Actual DOE School Grade"
doe_ela doe_ela_gain doe_ela_bq 
doe_mth doe_mth_gain doe_mth_bq 
doe_sci doe_scs 
doe_accel_ms doe_grad_rate doe_accel_hs 
doe_points doe_components doe_per_points doe_grade)
spacer1
('2018-19 Mid-Year Simulated School Grade' 
pred_ela_achv pred_ela_gain pred_ela_bq 
pred_mth_achv pred_mth_gain pred_mth_bq 
pred_sci_achv pred_scs_achv 
pred_accel_ms pred_grad_rate pred_accel_hs 
points components per_points grade grade_bkgd)
sort
); 
define sort / order noprint;
define region_sup / group style(column)={width=3.0cm just=c vjust=m} 'Regional~Superintendent';
define xo / group style(column)={width=3.5cm just=c vjust=m} 'Executive~Director';
define board_member / group style(column)={width=2.8cm just=c vjust=m} 'Board~Member';
define school_number_name/ group style(column)={width=7.6cm just=l} 'School Number/Name';

define doe_ela / display style(column)={background=#DDEBF7 width=1.0cm just=c} style(header)={background=#DDEBF7 tagattr='Format:@ rotate:90'}'ELA Achievement'; 
define doe_ela_gain / display style(column)={background=#DDEBF7 width=1.0cm just=c} style(header)={background=#DDEBF7 tagattr='Format:@ rotate:90'}'ELA Gains'; 
define doe_ela_bq / display style(column)={background=#DDEBF7 width=1.0cm just=c} style(header)={background=#DDEBF7 tagattr='Format:@ rotate:90'}'ELA BQ Gains'; 
define doe_mth / display style(column)={background=#DDEBF7 width=1.0cm just=c} style(header)={background=#DDEBF7 tagattr='Format:@ rotate:90'}'Math Achievement'; 
define doe_mth_gain / display style(column)={background=#DDEBF7 width=1.0cm just=c} style(header)={background=#DDEBF7 tagattr='Format:@ rotate:90'}'Math Gains'; 
define doe_mth_bq / display style(column)={background=#DDEBF7 width=1.0cm just=c} style(header)={background=#DDEBF7 tagattr='Format:@ rotate:90'}'Math BQ Gains'; 
define doe_sci / display style(column)={background=#DDEBF7 width=1.0cm just=c} style(header)={background=#DDEBF7 tagattr='Format:@ rotate:90'}'Science Achievement'; 
define doe_scs / display style(column)={background=#DDEBF7 width=1.0cm just=c} style(header)={background=#DDEBF7 tagattr='Format:@ rotate:90'}'Social Studies Achievement'; 
define doe_accel_ms / display style(column)={background=#DDEBF7 width=1.0cm just=c} style(header)={background=#DDEBF7 tagattr='Format:@ rotate:90'}'MS Acceleration'; 
define doe_grad_rate / display style(column)={background=#DDEBF7 width=1.0cm just=c} style(header)={background=#DDEBF7 tagattr='Format:@ rotate:90'}'Graduation Rate 1617'; 
define doe_accel_hs / display style(column)={background=#DDEBF7 width=1.0cm just=c} style(header)={background=#DDEBF7 tagattr='Format:@ rotate:90'}'College & Career~Acceleration 1617';
define doe_points / display style(column)={background=#DDEBF7 width=1.0cm just=c} style(header)={background=#DDEBF7 tagattr='Format:@ rotate:90'}'Total Points Earned'; 
define doe_components / display style(column)={background=#DDEBF7 width=1.0cm just=c} style(header)={background=#DDEBF7 tagattr='Format:@ rotate:90'}'Total Components'; 
define doe_per_points / display style(column)={background=#DDEBF7 width=1.0cm just=c} style(header)={background=#DDEBF7 tagattr='Format:@ rotate:90'}'% of Total Possible Points';
define doe_grade / display style(column)={background=#DDEBF7 width=1.0cm just=c} style(header)={background=#DDEBF7 tagattr='Format:@ rotate:90'}'School Grade';

define spacer1 / display style(column)={width=1.0cm just=c} style(header)={tagattr='Format:@ rotate:90'}'';

define pred_ela_achv / display style(column)={width=1.0cm just=c} style(header)={tagattr='Format:@ rotate:90'}'ELA Achievement'; 
define pred_ela_gain / display style(column)={width=1.0cm just=c} style(header)={tagattr='Format:@ rotate:90'}'ELA Gains';
define pred_ela_bq / display style(column)={width=1.0cm just=c} style(header)={tagattr='Format:@ rotate:90'}'ELA BQ Gains';
define pred_mth_achv / display style(column)={width=1.0cm just=c} style(header)={tagattr='Format:@ rotate:90'}'Math Achievement';
define pred_mth_gain / display style(column)={width=1.0cm just=c} style(header)={tagattr='Format:@ rotate:90'}'Math Gains';
define pred_mth_bq / display style(column)={width=1.0cm just=c} style(header)={tagattr='Format:@ rotate:90'}'Math BQ Gains';
define pred_sci_achv / display style(column)={width=1.0cm just=c} style(header)={tagattr='Format:@ rotate:90'}'Science Achievement';
define pred_scs_achv / display style(column)={width=1.0cm just=c} style(header)={tagattr='Format:@ rotate:90'}'Social Studies Achievement'; 
define pred_accel_ms / display style(column)={width=1.0cm just=c} style(header)={tagattr='Format:@ rotate:90'}'MS Acceleration';
define pred_grad_rate / display style(column)={width=1.0cm just=c} style(header)={tagattr='Format:@ rotate:90'}'Graduation Rate 1718';
define pred_accel_hs / display style(column)={width=1.0cm just=c} style(header)={tagattr='Format:@ rotate:90'}'Projected College & Career~ Acceleration 1718';
define points / display style(column)={width=1.0cm just=c} style(header)={tagattr='Format:@ rotate:90'}'Total Points Earned'; 
define components / display style(column)={width=1.0cm just=c} style(header)={tagattr='Format:@ rotate:90'}'Total Components'; 
define per_points / display style(column)={width=1.0cm just=c} style(header)={tagattr='Format:@ rotate:90'}'% of Total Possible Points';
define grade / display style(column)={width=1.0cm just=c} style(header)={tagattr='Format:@ rotate:90'}'School Grade';
define grade_bkgd / noprint;

compute grade / char;
if grade='F' then do;
call define ("grade","style","style={background=red}");
end;
endcomp;

compute grade_bkgd;
if grade_bkgd.sum = 1 then do;
call define ("grade","style","style=[background=#A9D08E]");
end; 
if grade_bkgd.sum = -1 then do;
call define ("grade","style","style={background=rose}");
end;
endcomp;

compute board_member / char;
if board_member ~= '' then do;
call define (_row_,"style","style={bordertopwidth=2px}");
end;
endcomp;
compute xo / char;
if xo ~= '' then do;
call define (_row_,"style","style={bordertopwidth=3px}");
end;
endcomp;
compute region_sup / char;
if region_sup ~= '' then do;
call define (_row_,"style","style={bordertopwidth=4px}");
end;
endcomp;

run; 



ods _all_ close;

[Reeza]

FYI - I've moved the post to the ODS forum.

[Cynthia_sas · Accepted solution]

Hi:

  Using 9.4 M5 and ODS EXCEL or ODS TAGSETS.EXCELXP, the second example of PROC REPORT code works for me and produced this output:

For Sheet 2 in both Excel files. I know it's a bit garishly colored in the background, but the colors who exactly where the change was coded.

ods html path='c:\temp' file='span_hdr_back.html';
ods excel file='c:\temp\span_hdr_back.xlsx' options(sheet_name='NotWorkExcel');
ods tagsets.excelxp file='c:\temp\span_hdr_xp.xml' style=htmlblue
    options(sheet_name='NotWorkExcel');
ods escapechar='^';

** This top approach will work in ODS HTML, but NOT on sheet 1 in ODS EXCEL;
** or TAGSETS.EXCELXP;
proc report data=sashelp.shoes;
  where region in ('Canada', 'Pacific');
  column ('^{style[background=yellow]Spanning Header}' product) region,(sales returns);
  define product / group;
  define region/across ' ';
run;

ods excel options(sheet_name='YesWorkExcel');
ods tagsets.excelxp options(sheet_name='YesWorkExcel');

** This technique works in 9.4 M5, but might not work in earlier versions 
   of SAS or in all destinations. It does work for ODS HTML.;
** Look at sheet 2 to see it work for  ODS EXCEL or ODS TAGSETS.EXCELXP;
proc format;
  value $hdr 'Spanning Header'='yellow'
             'Product' = 'lightgreen'
             'Total Sales' = 'lightpink'
             'Total Returns' = 'lightblue'
             'Canada' = 'lavender'
             'Pacific' = 'cyan';
run;

proc report data=sashelp.shoes 
  style(header)={background=$hdr.};
  where region in ('Canada', 'Pacific');
  column ('Spanning Header' product) region,(sales returns);
  define product / group;
  define region/across ' ';
run;
 
ods html close;
ods excel close;
ods tagsets.excelxp close;

  Just a caveat that this may not work in all destinations or in earlier versions of SAS.

Hope this helps,

Cynthia

[cuevasj]

This worked for issues number one and two. Thank You.
Any suggestions for issue 3? or should I mark this as accepted solution and post new question for number three?

[Cynthia_sas]

Hi:
Honestly, I'd recommend that you work with Tech Support on #3. I don't work with frozen headers in Excel, so I don't have any suggestions for working with frozen headers. Someone else might, but I'd recommend posting a smaller example that shows the issue with SASHELP data, since everybody has access to that data and no one can run your code without making some very complicated test data.
Cynthia