topic Re: How do I control the spacing in the header cell for my Proc Report that is exported in Excel? in SAS Programming

How do I control the spacing in the header cell for my Proc Report that is exported in Excel?

InspectahDex — Tue, 28 Feb 2023 19:59:43 GMT

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;

Re: How do I control the spacing in the header cell for my Proc Report that is exported in Excel?

Cynthia_sas — Tue, 28 Feb 2023 23:22:17 GMT

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

Re: How do I control the spacing in the header cell for my Proc Report that is exported in Excel?

InspectahDex — Tue, 28 Feb 2023 23:41:32 GMT

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