Your SAS programs, embedded in web apps and elsewhere

Receiving Error Notification while opening Excel file generated out of SAS Stored Process.

Reply
Occasional Contributor
Posts: 15

Receiving Error Notification while opening Excel file generated out of SAS Stored Process.

Hello SAS Experts,

Could any of you please help me out with this issue I'm facing?

1. I have a SAS stored process created out of a simple SAS Code.

2. The SAS Stored process is required to generate an Excel file as output comtaining the needed data.

3. In order to explicitly stream the results in to an excel file post needed processing, I edited the SAS stored process code(from step 2) manually after it was created out of my original code (from step 1) by adding these codes 

%let _odedest = tagsets.excelXP;

%let _odsstyle=;

%let _odsoptions=;

%let rv=%sysfunc(appsrv_header(content-type,application/vnd.ms-excel));

%let rv=%sysfunc(appsrv_header(content-disposition,%str(attachment; filename=ABCDEFG.xls)));

before the SAS Stored Process generated codes  %STPBEGIN, and *ProcessBody; comment.

4. While executing my stores process, since I included these at the very top, after just a few moments of clicking the report a prompt appears asking whether to Save or Open ABCDEFG.xls - which is Okay

5. Irrespecive of whatever I choose (save or open) at the end of the processing, while opening the results I get this meesage

THE FILE YOU ARE TRYING TO OPEN, ABCDEFG.xls IS IN A DIFFERENT FORMAT THAN SPECIFIED BY THE FILE EXTENSION. VERIFY THAT THE FILE IS NOT CORRUPTED AND IS FROM A TRUSTED SOURCE BEFORE OPENING THE FILE> DO YOU WANT TO OPEN THE FILE NOW?

6. I just ignore this message and open the file and it shows my expected results with all data without any issues at all. However, my customers find this message very suspscious and revert often asking questions.

7. I tried changinf the extension from .xls to .xml in the line  %let rv=%sysfunc(appsrv_header(content-disposition,%str(attachment; filename=ABCDEFG.xls))); But, then it will not be saved as .xls file which originaly was the requirement.

8. Could anybody please suggest a way whereby I could still maintain the saving of results as an excel file itself instead of xml while overcoming the message state?

Thanks,

Sarath

Super User
Posts: 10,490

Re: Receiving Error Notification while opening Excel file generated out of SAS Stored Process.

The message is a Microsoft office bit. The tagsets.excelxp actually creates XML formatted data that Excel can read.

Since you have named the file with an XLS extension not XML then the later versions of Office applications give you this snarky message.

If you license the module for access to PC Files then depending on what you are doing, Proc Export can generate XLS files.

Or open the file in Excel and save as XLS. There have been several posts in this forum about having Excel macro or VB code to do this.

SAS Super FREQ
Posts: 8,743

Re: Receiving Error Notification while opening Excel file generated out of SAS Stored Process.

I believe there is also a way to get rid of the snarky message by changing the Windows registry per this note 31956 - Using the MSOffice2k ODS destination with Microsoft Excel 2007 generates a dialog box  -- the note outlines the message as happening with ODS HTML and ODS MSOFFICE2K -- it is the same message you get with TAGSETS.EXCELXP, too. Basically anytime you provide a file extension that doesn't "match" what Microsoft finds the file contents to be. However, I really don't recommend going down the Windows Registry change road. I think either educating your users to click Yes and then doing a File --> Save AS or making your Excel macro or VB script do the change. I understand this is not as easy when the file is created in a stored process, but educating your users is the cheapest, fastest, most straightforward method of all.

 

cynthia

Occasional Contributor
Posts: 15

Re: Receiving Error Notification while opening Excel file generated out of SAS Stored Process.

Thanks Ballardw, Thanks Cynthia.. Will try some other workarounds....

Ask a Question
Discussion stats
  • 3 replies
  • 1500 views
  • 3 likes
  • 3 in conversation