BookmarkSubscribeRSS Feed
Sarath
Calcite | Level 5

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

3 REPLIES 3
ballardw
Super User

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.

Cynthia_sas
SAS Super FREQ

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

Sarath
Calcite | Level 5

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

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 2999 views
  • 3 likes
  • 3 in conversation