BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
pkm_edu
Quartz | Level 8
  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.

 

pkm_edu_0-1692885162527.png

 

pkm_edu_1-1692885162529.png

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Quentin
PROC Star

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.

Check out the Boston Area SAS Users Group (BASUG) video archives: https://www.basug.org/videos.

View solution in original post

11 REPLIES 11
Quentin
PROC Star

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

Check out the Boston Area SAS Users Group (BASUG) video archives: https://www.basug.org/videos.
Quentin
PROC Star

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.

Check out the Boston Area SAS Users Group (BASUG) video archives: https://www.basug.org/videos.
pkm_edu
Quartz | Level 8

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
Quartz | Level 8

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

Quentin
PROC Star

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.

Check out the Boston Area SAS Users Group (BASUG) video archives: https://www.basug.org/videos.
pkm_edu
Quartz | Level 8
But, the Python-generated Excel file has no "! Headers and Fotters" warning from the Inspector.
Quentin
PROC Star

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?

Check out the Boston Area SAS Users Group (BASUG) video archives: https://www.basug.org/videos.
pkm_edu
Quartz | Level 8

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
Quartz | Level 8

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

Quentin
PROC Star

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.

Check out the Boston Area SAS Users Group (BASUG) video archives: https://www.basug.org/videos.
pkm_edu
Quartz | Level 8

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.

 

SAS INNOVATE 2024

Innovate_SAS_Blue.png

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. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 302 views
  • 0 likes
  • 2 in conversation