BookmarkSubscribeRSS Feed
FrankE
Fluorite | Level 6

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!!

9 REPLIES 9
FrankE
Fluorite | Level 6

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?

Cynthia_sas
SAS Super FREQ

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

FrankE
Fluorite | Level 6

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?

NN
Quartz | Level 8 NN
Quartz | Level 8

Hi,

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

or Sas Information Delivery Portal.

Cynthia_sas
SAS Super FREQ

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

FrankE
Fluorite | Level 6

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

Cynthia_sas
SAS Super FREQ

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 this:(assumes 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

Cynthia_sas
SAS Super FREQ

trying again to post file with example URL...

cynthia

cociotho
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 2059 views
  • 0 likes
  • 4 in conversation