How to make SSP to return the results to user not as html, but to excel.

[deleted_user]

Dear professionals,
Could you please help me to solve a problem:
Users in our company use SAS portal to access SSP. In some cases they need that SSP would let them to save the returned date in excel file. The problem is that if there is a way to ask a SSP to return data not in html format but in MIME type Ms/Excel. That the browser would not display the data returned from SSP, but instead would launch the Ms excel application.

Thank you in advance

[Cynthia_sas]

You must use the STPSRV_HEADER function to send the appropriate MIME header. I show the use of the MSOFFICE2K tagset because Excel and Word are "happier" with HTML that conforms to the Microsoft HTML spec than with standard HTML 4.0 tags.


Execute this stored process ONLY from within web-based clients such as the Information Delivery Portal or the Stored Process Web Application. Since the MSOFFICE2K tagset is an HTML-based tagset, the use of STPSRV_HEADER allows your SP to signal the browser about what application to launch to open the content. This version of the Stored Process will NOT work in the Add-in for Microsoft Office or in Enterprise Guide.

[pre]

*ProcessBody;
data _null_;
rc = stpsrv_header('Content-type','application/vnd.ms-excel;');
rc = stpsrv_header('Content-disposition','attachment; filename=temp.xls');
run;



%let _odsdest=tagsets.msoffice2k;
%let _odsstyle=sasweb;
%stpbegin;

proc print data=sashelp.class noobs;
title 'test mimetype=application/vnd.ms-excel';
run;

proc freq data=sashelp.class;
title 'Proc Freq';
tables height;
run;

proc means data=sashelp.class min max median std;
var height age;
title 'Proc Means';
run;

%stpend;
[/pre]

After you create and register this stored process, you can execute it from the portal. If the receiving browser is on a system with Excel, then Excel should launch. You need Office 2000 or higher to read Microsoft HTML. The MIME type header is usually assumed to be HTML (depending on the client application making the request) -- so when you invoke the function, as shown in the DATA _NULL_ program, you are essentially changing the default MIME type header.

cynthia

[deleted_user]

I tried to utilize the code provided and I am recieving errors.....

WARNING: No logical assign for filename _WEBOUT.
WARNING: No logical assign for filename _WEBOUT.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds


25 +%let _odsdest=tagsets.msoffice2k;
26 +%let _odsstyle=sasweb;
27 +%stpbegin;
28 +proc print data=sashelp.class noobs;
29 +title 'test mimetype=application/vnd.ms-excel';
30 +run;

The SAS System

WARNING: No output destinations active.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds


30 !+ proc freq data=sashelp.class;
31 +title 'Proc Freq';
32 +tables height;
33 +run;

WARNING: No output destinations active.
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: PROCEDURE FREQ used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds


34 +proc means data=sashelp.class min max median std;
35 +var height age;
36 +title 'Proc Means';
37 +run;

WARNING: No output destinations active.
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: PROCEDURE MEANS used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds


38 +%stpend;
39 +
40 +* Begin EG generated code (do not edit this line);
41 +*';*";*/;run;
42 +
43 +* End EG generated code (do not edit this line);
NOTE: %INCLUDE (level 1) ending.

I have Microsoft office excel 2003 on my machine.
After creating the stored process I ran it on the sasportal via IE.
If at all possible you could you tell me what i am doing wrong. I would greatly appreciate it.

[Cynthia_sas]

Hi:
I am not sure what's up with your test. When I wrote the original stored process, I tested it using the SAS Information Delivery Portal with Excel 2002 AND Excel 2003. Remember, however, that you would NOT be using the SAS Add-in for Microsoft Office for this Stored Process. You would ONLY be able to run this stored process from the Information Delivery Portal. I don't know whether this stored process would work in EG, but I suspect that it will not.

Generally speaking, you only need to specify or change the MIME type header when you want to launch a viewer other than the browser for the content that is returned from the stored process.

Did you perhaps try to submit this code from inside a SAS session or an EG code node? How did you register the stored process? I just tested it again using the SAS Information Delivery Portal and a result type of STREAMING for the stored process and the portal launched the download dialog box, I clicked Open and Excel opened my output. So, I'm stumped on why this didn't work for you. I think your best bet for an answer is to contact SAS Tech Support, because they can walk you through the steps to verify your configuration and installation of the Information Delivery Portal -- check repositories, check SP registration, etc.

To find out how to contact SAS Technical Support, refer to: http://support.sas.com/techsup/contact/index.html

Good luck,
cynthia

[Vince_SAS]

Chances are, the stored process is being run on a Workspace Server, which does not support "streaming" output. That would explain why _WEBOUT is not defined.

Vince
SAS R&D

[deleted_user]

Dear Cynthia,
many thanks to you for your help 🙂 It worked !!! 🙂

One more question about encoding. SSP returns the text encoded in utf-8 ant the browser displays Baltic letters correctly. I have tried to set in the http header the encoding value to utf-8 in SSP that way :
data _null_;

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

run;

But it did not worked. Then I tried to set the SSP parameter _ENCODING=”wbaltic”

It also did not help.

Do you have any ideas how to make an excel to open data in Baltic encoding ?

Thank you a lot.
Sarunas

[Cynthia_sas]

Sarunas:
Sadly, I do not know very much about encoding and language/font issues. Since you are essentially creating an HTML file and then opening that HTML file with Excel (by setting STPSRV_HEADER), it's hard for me to figure whether it's an HTML encoding issue or an Excel issue. I think that ENCODING= only "belongs" to the text/html content type. I do not know whether it's valid with the application/vnd.??? content type.

Do your other stored processes (the ones that are returned as HTML) display in the browser with the correct Baltic encoding?

If yes, then my guess would be that the issue is between Excel and the HTML that's getting generated. However, that is just a guess. At this point, you have crossed over into needing Technical Support.

To open a track with Tech Support, go to this site:
http://support.sas.com/ctx/supportform/index.jsp

Good luck!
cynthia

[deleted_user]

Dear Cynthia,
thank you for your message 🙂
Well, eventually I have made my SSP to send the content encoded with wbaltic symbols 🙂 The problem was that I forgot to replace the corrected SSP program file in SSP server 🙂 When I replaced the file everything worked correctly. 🙂

Thank you a lot for your assistance,
best regards
Sarunas