The SAS Output Delivery System and reporting techniques

Output excel file is not generating from stored procedure created using SAS EG

Reply
Occasional Contributor
Posts: 12

Output excel file is not generating from stored procedure created using SAS EG

Hello everyone,

 

Again i am hit with one strange error.

 

I am creating a report using sas stored procedure to get the ouput in excel spreadsheet from running this stored procedure from web application. while i have used same code which i have used in previous reports and those are giving result absolutely fine.

 

I am using below code:

 

data _null_;

 rc = stpsrv_header('Content-disposition','attachment; filename=test.xls');

 rc = stpsrv_header('Content-disposition',"inline");

 run;

 

OPTIONS NOBYLINE MISSING=' ';

/*ods tagsets.ExcelXP options(*/

%let _ODSDEST=TAGSETS.EXCELXP options(

EMBED_TITLES_ONCE = 'YES'

EMBEDDED_TITLES='YES'

/*EMBEDDED_FOOTNOTES='YES' */

 

sheet_name='Current Data'

/*autofilter='ALL' */

/*frozen_headers='3' */

/*frozen_rowheaders='NO' */

 

ABSOLUTE_COLUMN_WIDTH='13,10,11,12,10,12,12,12,12,12,11,11,11,16,16,16,10,10,10,10,10,10,10'

 

/*ZOOM='80' */

/*WRAPTEXT='YES' */

BLACKANDWHITE='YES'

);

%let _ODSSTYLE=seaside;

 

Then MACRO Code to run report.

 

I am getting below error rather than getting output in excel file.

 

<?xml version="1.0" encoding="utf-8" ?>

  <?mso-application progid="Excel.Sheet"?>
- <!--
 Generated by the SAS Excelxp Tagset .v1.131, 04/23/2015 
  -->
- <Workbook xmlns="urn:schemas-microsoft-comSmiley Surprisedffice:spreadsheet" xmlns:x="urn:schemas-microsoft-comSmiley Surprisedffice:excel" xmlns:ss="urn:schemas-microsoft-comSmiley Surprisedffice:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">
- <DocumentProperties xmlns="urn:schemas-microsoft-comSmiley Surprisedffice">
  <Author>sassrv</Author>
  <LastAuthor>sassrv</LastAuthor>
  <Created>2017-09-28T08:16:45</Created>
  <LastSaved>2017-09-28T08:16:45</LastSaved>
  <Company>SAS Institute Inc. http://www.sas.com</Company>
  <Version>9.04.01M4P11092016</Version>
  </DocumentProperties>
- <Styles>
- <Style ss:ID="_body">
 
 
 
Can anyone suggest what should i do to get rid of this and get output in excel spreadsheet.
 
Thanks in advance !!
Super User
Posts: 8,302

Re: Output excel file is not generating from stored procedure created using SAS EG

What you get is Excel-compatible XML. Saving that as a .xml file and opening with Excel should work.

 

Do not name output created with tagsets.excelxp with a .xls extension, as you are not creating an Excel file, but XML text. It's just that the XML contains Excel-compatible data.

 

You also can't have both content-dispositions (see https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/Content-Disposition). The second overrides the first and directs your browser to display the XML text inline, which it dutifully does. If you only have "attachment" (and use the CORRECT extension .xml!!), the browser will then prompt you for storing or opening the file.

 

Alternatively, consider using another means of transfer to Excel, like proc export with the xlsx engine.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 1 reply
  • 167 views
  • 0 likes
  • 2 in conversation