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