This is the top portion of the Excel file created using ODS Excel and Proc Report. Notice that the spacing is odd in the header row.
This is the desired output:
What do I need to modify in my code:
ods excel file="&datapath.\Results\CostMitigationResults_&thiswk._ALL.xlsx"
options(sheet_name='AllInitiativeDetail'
frozen_headers = 'yes');
options missing=0;
proc report data=ss.AllDetail
style(header)=[background=#DAEEF3 fontweight=bold foreground=black]
style(summary)=[background=#92CDDC fontweight=bold foreground=black];
column ('Cost Mitigation for All Provider Contracting Agreements and Initiatives'
Region
'Hospital or Provider Name'n
Initiative
'Effective Month/Year'n
'2021 Cost Mitigation'n
'2022 Cost Mitigation'n
'2023 Cost Mitigation'n
'2024 Cost Mitigation'n
Total
'Previous Total'n
'Variance to Prior Week'n);
define Region / display;
define 'Hospital or Provider Name'n / display;
define Initiative / display;
define '2021 Cost Mitigation'n / analysis;
define '2022 Cost Mitigation'n / analysis;
define '2023 Cost Mitigation'n / analysis;
define '2024 Cost Mitigation'n / analysis;
define Total / analysis;
define 'Previous Total'n / analysis;
define 'Variance to Prior Week'n / display;
compute 'Variance to Prior Week'n;
if 'Variance to Prior Week'n ne 0 and 'Variance to Prior Week'n ne "" then call define(_row_,"style","style={background=cxEEFFFF}");
endcomp;
run;
Hi: I cannot duplicate your experience. I am using SAS 9.4 M7 with ODS EXCEL. Since you didn't post data, I used SASHELP.SHOES and just defined aliases so I could keep using sales, inventory and returns over and over. I don't have any issues, as shown below:
You can see that I've scrolled because I had obs=10 and I've scrolled up to show the frozen headers.
Here's the code I used.
If you are not getting the same results, I'd suggest opening a track with Tech Support so they can look at ALL your code and some of your sample data and see if they can duplicate your results.
Cynthia
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.