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

2025 SAS Hackathon: There is still time!

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!

Register Now

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
  • 1033 views
  • 0 likes
  • 2 in conversation