I have an excel report that I need to automate via SAS. Once exported, the report should have a tab for every month where data exists. The tab and header should indicate the month name. Currently I have everything working except the month name. The report currently displays the month number instead. Below is my current SAS Code and a screenshot of the current output:   %macro get_report(); ods excel file='/prd/saucgo/reporting/summary/output/Monthly_Schedule.xlsx' options(sheet_name='#byvar' /*'#byval1'*/ embedded_titles='yes' embedded_footnotes='yes' tab_color='purple' start_at='2,4' frozen_headers='yes' title_footnote_nobreak='yes' autofilter='1-10' absolute_column_width='16'); title 'Monthly Schedule' ; /* footnote 'This footnote will appear in the worksheet';*/ proc report data=Monthly_Schedule; by Mo; column Origin Destination Flight_Number Depart_Time Arrival_Time Aircraft Start_Date End_Date DOW_Ops Group; define Flight_Number / display 'Flight#'; define SCH_AC_DPRT_TIM / display 'Depart_Time'; define SCH_AC_ARR_TIM / display 'Arrival_Time'; define Yr / group; define Mo / group; run; proc report data=_Mth_Schd; column sch_dprt_stn_cd sch_arr_stn_cd LegStartDte LegEndDte Body; define sch_dprt_stn_cd / display 'Departure'; define sch_arr_stn_cd / display 'Arrival'; define LegStartDte / display 'Leg Start Date'; define LegEndDte / display 'Leg End Date'; ods excel close; %mend; ... View more

I'm new to SAS but was able to create a report. I've included a small screenshot below. The issue that I have is with the conditional formatting for cell background color. I'd like to change the background color to grey for any cell that does not contain a value. As you can see from the screenshot, I was able to accomplish this for Mean and StdDev but failed to attain success with the Pax LF field. I'm including my code as well and any help would be much appreciated. col (Region_Cat Orig_Stn_Cd Dest_stn_cd CgCpFc_AcTp_Cd ('Last 30 Days' Mean_Kg_Cpty StdDev_Cap_kgs) Schd_dprt_gdt,( Pax_Lf Capacity_kg)) ; define Region_Cat / "Region" group center style(column)=[cellwidth=1in]; define Orig_Stn_Cd / "Orig" group center style(column)=[cellwidth=.45in]; define Dest_stn_cd / "Dest" group center style(column)=[cellwidth=.45in]; define CgCpFc_AcTp_Cd / "Fleet" group center style(column)=[cellwidth=0.45in] ; define Mean_Kg_Cpty / "Mean (kg)" group format=comma7. center style(column)=[cellwidth=.45in]; define StdDev_Cap_kgs / "StdDev (kg)" group format=comma7. center style(column)=[cellwidth=0.45in] ; define Schd_dprt_gdt / " " across center style(header)=[borderleftwidth=2 borderleftcolor=#C01933 /*cellwidth=1in*/ borderrightwidth=2 borderrightcolor=#C01933]; define Pax_Lf / analysis mean "Pax LF" format=Percent10. center style(column)=[borderleftwidth=2 borderleftcolor=#C01933] center style(column)=[borderleftwidth=2 borderleftcolor=#C01933] center style(header)=[borderleftwidth=2 borderleftcolor=#C01933]; define Capacity_kg / analysis mean "Capacity (kg)" format=comma7. center style(header)=[borderrightwidth=2 borderrightcolor=#C01933] style(column)=[borderrightwidth=2 borderrightcolor=#C01933]; compute Orig_Stn_Cd; if (Orig_Stn_Cd NE '') then tmp_orig =Orig_Stn_Cd; if (Orig_Stn_Cd EQ '') then Orig_Stn_Cd=tmp_orig; endcomp; compute Dest_stn_cd; if (Dest_stn_cd NE '') then tmp_dest =Dest_stn_cd; if (Dest_stn_cd EQ '') then Dest_stn_cd=tmp_dest; endcomp; compute CgCpFc_AcTp_Cd; if (CgCpFc_AcTp_Cd NE '') then tmp_AC =CgCpFc_AcTp_Cd; if (CgCpFc_AcTp_Cd EQ '') then CgCpFc_AcTp_Cd=tmp_AC; endcomp; compute StdDev_Cap_kgs; if (StdDev_Cap_kgs=.) then call define (_col_,"style", "style={background=grey}") ; endcomp; compute Mean_Kg_Cpty; if (Mean_Kg_Cpty=.) then call define (_col_,"style", "style={background=grey}") ; endcomp; compute Pax_Lf; if (compress(Pax_Lf)=.) then call define (_col_,"style", "style={background=grey}") ; endcomp; ... View more