How to resolve the "! Headers and Footers", etc. issue in the SAS-generated Excel File

[pkm_edu]

1. I have created an Excel file using the SAS code inserted here.

   /* Direct proc report output to excel */
   ods listing close;
   ods excel file = "&path\SAS_Solution_WS_&formatted_date..xlsx" 
      options (sheet_name = 'Sheet1'
      flow="header,data" row_heights = '15'
      absolute_column_width='11,11,70,30,55'); 
   proc report data=meps_zip_links; 
     column data_year puf_num meps_file file_format  zip_link;
     define puf_num / display ;
     define meps_file / display;
     define data_year / display;
     define file_format / display;
     define zip_link / display ;
     compute zip_link ;
       call define(_col_,"url",zip_link);
     endcomp;
   run;
   ods excel close;
   ods listing;
   

2. I opened  SAS_Solution_WS_2023-08-12.xlsx in Excel.
3. Then I did this

 File -> Info -> Inspect Workbook -> Inspect Document -> Inspect -> Wait ->

Issue: I see the two items with an exclamation mark.

Question: What would I do in my SAS code so the SAS-generated Excel file does not render the above issue?

[Quentin]

I think the "Headers" warning is just saying that the file has a header.  So if you don't want a header you can use a title; statement to clear the titles and you shouldn't get that message.

I don't know if there is a way to keep the file path out of the metadata / file properties.  Will be interested to see thoughts from others.  Worst case, you could drop into using SAS to edit the zipped xml.  There was an interesting thread on this: https://communities.sas.com/t5/ODS-and-Base-Reporting/Excel-with-Custom-Property-Names/m-p/842709

[Quentin · Accepted solution]

I used ODS Excel to generate an XLSX file and then ran the inspector and got the same warning about it having an absolute path.  But then I manually unzipped the XLSX file and searched the contents, and couldn't find a path anywhere.  So could be a false positive from the inspector.

[pkm_edu]

Your comment (false positive) regarding the warning about the absolute path for the SAS-generated Excel file is interesting.  I got the same warning the for the Python-generated Excel file from the analysis of the same data  when checking it in Excel.

However, the real issue is that I got the "! Header and Footers" warning only for the SAS-generated Excel file, not for the Python-generated Excel file.  This issue seems to cause an warning message when comparing the two Excel files using a Python Program. 

The question is, What changes do I make to the SAS code submitted to avoid the warning from the  Excel "File->Info->Inspect Workbook->Inspect Document->Inspect"?

[pkm_edu]

I do want the header, not the warning from the running of the Inspector in Excel.

[Quentin]

I think the meaning of the Header warning from Inspector is just "your file has a header".  Not sure why MS would think they should warn people of that.  

If I just open Excel, create a file with a header that says 'hello world', save it, and run the inspector, I get the same warning.

[pkm_edu]

But, the Python-generated Excel file has no "! Headers and Fotters" warning from the Inspector.

[Quentin]

Does the python file have the header?  

If you try my test (No SAS or Python, just use Excel to create an excel file with a header), do you also get the header warning from the inspector?

[pkm_edu]

The python-generated Excel file has no header warning.  

If I test an Excel file with headers created using Excel (not created using SAS or Python), I don't get the header warning from the inspector.

[pkm_edu]

Furthermore, The python-generated Excel file has the headers.  

[Quentin]

Interesting.  I think I'm out of ideas.  Maybe you could dig into the microsoft side and ask them what triggers the Headers warning.  Since I get it from even an Excel-created XLSX file, I can't even help test, unfortunately.

[pkm_edu]

Apologies for posting the reply here after replying via email.

But, the Python-generated Excel file has no  “! Headers and Footers” warning from the Inspector.