09-29-2011 12:28 PM
I have the following stored process which I am executing in Enterprise Guide:
rc = stpsrv_header('Content-type',"application/vnd.ms-excel");
rc = stpsrv_header('Content-disposition',"attachment; filename=ex_xp1.xls");
%let _odsdest = tagsets.excelxp;
%let _odsstyle = sasweb;
proc print data=sashelp.class noobs;
title 'test 1 to Excel using stpbegin';
When I execute this I get the error in the log "Unrecognized option: ROLAP" and the results windows shows the raw XML.
How do I?:
1) get the results window to show the output in Excel
2) get the "Open/Save As" diaglog to pop up?
09-29-2011 01:36 PM
Ok to partially answer my own question. The option "rolap="on" was being automatically set up by Enterprise Guide as seen in my log:
>>> SAS Macro Variables:
so I had to add the line: %let _ODSOPTIONS =; to clear that out. Now the code runs clean but the Results tab still just shows the XML and won't either show Excel in the results tab or pop up the "Open/Save As" dialog. What am I missing?
09-30-2011 01:39 AM
SAS Enterprise Guide wants all XML to be SASReport XML. It doesn't like to open TAGSETS.EXCELXP output....or didn't like TAGSETS.EXCELXP "flavor" of XML, per this note:
As far as I know, the only client application that can handle an &_ODSDEST value of TAGSETS.EXCELXP is the SAS Information Delivery Portal. This forum has several previous postings about using TAGSETS.EXCELXP.
09-30-2011 02:00 PM
Thanks for getting back to me. I actually stole my example from a prior post that you had written on a simular subject. Even if I can't veiew the ExcelXP output in the results window, shouldn't the "Open/Save As" dialog pop up?
10-01-2011 10:11 AM
I think the Open/Save As dialog box is browser behavior when the browser client app receives the MIME header (what you set with STPSRV_HEADER). When you use STPSRV_HEADER, the assumption is that you are using the HTTP protocol to return the SP results to a client app (like a browser or the Stored Process Web App or a custom web app) that knows how to use helper plug-ins to open MIME type output.
SAS Enterprise Guide will only open certain types of output from SPs: SASReport XML, HTML, PDF, and RTF results -- but you do not use STPSRV_HEADER when you write an SP to use in EG. Word, for example, will accept/open stored process results of SASReport XML, HTML and RTF -- and again, you do not need to use STPSRV_HEADER with an SP meant to be executed in Word. Basically, you need STPSRV_HEADER for client apps (like browsers) that are using the HTTP protocol to receive stored process results. Since EG isn't a browser, it isn't using the HTTP protocol to receive and open and render stored process results. So, I do not believe you will get the dialog you expect.
Have you tried to execute your SP using the Information Delivery Portal or the Stored Process Web App or via a URL call to SASStoredProcess/do? I would expect you to see that dialog box with any of those submission methods.
10-03-2011 10:32 AM
@NN - This is in EG.
Cynthia - Thanks for your help. So if I cannot use EG to open an ExcelXP output how would I develop this in EG? Is there a perferred method?
10-03-2011 12:32 PM
You can develop your stored process in EG -- but you if your stored process uses STPSRV_HEADER, the assumption is that your users will be using a WEB client (like a browser, the Stored Process Web App or a custom web app or the information delivery portal) to submit the SP. In this case, the HTTP protocol would cause the receiving browser to open Excel when it received the TAGSETS.EXCELXP output from the server.
To test a SP via URL, you would have a URL something like thisassumes the SP is in the Foundation/SP/ structure in the metadata and that the name of the registered SP is MYSTP and the SP has 2 parameters: COUNTRY and YEAR). The URL would be constructed of the following parts. See attached TXT file.
It would probably in an HTML page that your users could get to. Then, assuming that all the configuration was in place, when they clicked on the link to the URL, they would be making a request for the SP to execute and when the server sent the results back to them, then see the popup window for Open/Save the results.
Message was edited by: Cynthia Zender to try to get sample URL to show in response
04-27-2012 09:45 AM
Not sure if you found a solution to your question above, since it's been 6 or so months. I have developed a stored process that exports a specific sas data-set to excel, opens the client application, all from a sas url path. You can also run this directly from EG as well. Please see the examples below;
http://sas server url path goes here:server port# goes here/SASPortal/Director?_directive=STPRun&_program=%2FShared+Data%2FSP+SWR+QMSYSA+TLD+Export
Or with the data filled in
I have also been able to export data directly to Excel from EG, see attached zip file...