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;
... View more