Your SAS programs, embedded in web apps and elsewhere

Stored process and ExcelXP tagset error and odd results

Reply
Contributor
Posts: 53

Stored process and ExcelXP tagset error and odd results

Hello,

I have the following stored process which I am executing in Enterprise Guide:

*ProcessBody;

data null;

rc = stpsrv_header('Content-type',"application/vnd.ms-excel");

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

run;

%let _odsdest = tagsets.excelxp;

%let _odsstyle = sasweb;

%stpbegin;

  proc print data=sashelp.class noobs;

    title 'test 1 to Excel using stpbegin';

  run;

%stpend;

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?

Thanks!!

Contributor
Posts: 53

Stored process and ExcelXP tagset error and odd results

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:

_ODSOPTIONS=options(rolap="on") ATTRIBUTES=("CODEBASE"="http://www2.sas.com/codebase/graph/v92/sasgraph.exe#version=9,2")

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?

SAS Super FREQ
Posts: 8,864

Stored process and ExcelXP tagset error and odd results

Hi:

  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:

http://support.sas.com/kb/32/924.html

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.

cynthia

Contributor
Posts: 53

Stored process and ExcelXP tagset error and odd results

Posted in reply to Cynthia_sas

Hi Cynthia,

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?

Regular Contributor
Regular Contributor
Posts: 166

Stored process and ExcelXP tagset error and odd results

Hi,

Just a quick question are you executing the stored process in Sas Enterprise Guide

or Sas Information Delivery Portal.

SAS Super FREQ
Posts: 8,864

Stored process and ExcelXP tagset error and odd results

Hi:

  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.

cynthia

Contributor
Posts: 53

Stored process and ExcelXP tagset error and odd results

Posted in reply to Cynthia_sas

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

SAS Super FREQ
Posts: 8,864

Re: Stored process and ExcelXP tagset error and odd results

Hi:

  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 thisSmiley Sadassumes 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.


Cynthia

Message was edited by: Cynthia Zender to try to get sample URL to show in response

SAS Super FREQ
Posts: 8,864

Re: Stored process and ExcelXP tagset error and odd results

Posted in reply to Cynthia_sas

trying again to post file with example URL...

cynthia

Attachment
Occasional Contributor
Posts: 7

Re: Stored process and ExcelXP tagset error and odd results

Hi FrankE,

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

http://rdsasap.testserver.com:9999/SASPortal/Director?_directive=STPRun&_program=%2FShared+Data%2FSP...

I have also been able to export data directly to Excel from EG, see attached zip file...

Attachment
Ask a Question
Discussion stats
  • 9 replies
  • 1006 views
  • 0 likes
  • 4 in conversation