BookmarkSubscribeRSS Feed
InspectahDex
Obsidian | Level 7

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.

 

InspectahDex_0-1677614221289.png

 

This is the desired output:

InspectahDex_1-1677614319628.png

 

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;

 

2 REPLIES 2
Cynthia_sas
Diamond | Level 26

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:

Cynthia_sas_0-1677626396244.png

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.

Cynthia_sas_1-1677626503261.png

 

  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

InspectahDex
Obsidian | Level 7
Thank you for that. I just sent an email to Tech Support - I didn't know that was an option before.

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1067 views
  • 0 likes
  • 2 in conversation