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;
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?
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.
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
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.
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"?
I do want the header, not the warning from the running of the Inspector in Excel.
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.
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?
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.
Furthermore, The python-generated Excel file has the headers.
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.
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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.