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

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

pkm_edu — Thu, 24 Aug 2023 13:55:52 GMT

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;

I opened SAS_Solution_WS_2023-08-12.xlsx in Excel.
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?

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

Quentin — Thu, 24 Aug 2023 14:27:23 GMT

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

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

Quentin — Thu, 24 Aug 2023 14:37:20 GMT

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.

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

pkm_edu — Thu, 24 Aug 2023 14:59:58 GMT

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"?

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

pkm_edu — Thu, 24 Aug 2023 15:03:20 GMT

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

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

Quentin — Thu, 24 Aug 2023 15:07:09 GMT

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.

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

pkm_edu — Thu, 24 Aug 2023 15:11:41 GMT

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

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

pkm_edu — Thu, 24 Aug 2023 15:14:11 GMT

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.

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

Quentin — Thu, 24 Aug 2023 15:15:07 GMT

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?

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

pkm_edu — Thu, 24 Aug 2023 15:55:36 GMT

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.

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

pkm_edu — Thu, 24 Aug 2023 16:55:33 GMT

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

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

Quentin — Thu, 24 Aug 2023 17:05:24 GMT

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.