Excel Output Type

[deleted_user]

Hi,

Does anyone has suggestion on how to modify the code below to generate a report in Excel while running stored process in IDP?

This is the code used currently.
%stpbegin;
ods listing close;

ods html;
%generate_invalid_date;
ods html close;

%stpend;

[Cynthia_sas]

Hi:
There have been previous forum postings on this subject:
http://support.sas.com/forums/thread.jspa?messageID=3244ಬ
http://support.sas.com/forums/thread.jspa?messageID=6632᧨
http://support.sas.com/forums/thread.jspa?messageID=6638᧮

Basically, you will need to make sure that you issue the right content/type header (MIME header) for your content, using the STPSRV_HEADER function in a data null. You also need to make sure you have a working SAS program to convert to a stored process. Usually, you do NOT use or need other ODS statements in your stored process code. So I would recommend removing the statements highlighted below:
[pre]
%stpbegin;
ods listing close; <----may not need this statement
depending on why it is here

ods html; <----delete this statement
%generate_invalid_date;
ods html close; <----delete this statement

%stpend;

[/pre]

That's because %stpbegin/%stpend really take the place of your typical ODS "sandwich" statements. The value for &_ODSDEST is set appropriately by the client application. So, for EG, the SP would return HTML results; for PPT, the SP would return SAS Report XML results.

If you want to control the default output type (which is HTML by default for the IDP), then you would need to do an override to the reserved macro variable &_ODSDEST. If, for example, you wanted to generate a CSV file or an MSOFFICE2k HTML file or a TAGSETS.EXCELXP (Spreadsheet Markup Language XML file), you could override &_ODSDEST for the Portal execution of the SP by doing this BEFORE the %stpbegin:
[pre]
%let _odsdest = msoffice2k;
%stpbegin;
...code...
%stpend;

OR

%let _odsdest = csvall;
%stpbegin;
...code...
%stpend;

OR

%let _odsdest = tagsets.excelxp;
%stpbegin;
...code...
%stpend;

[/pre]

To launch Excel when the results come back to the client browser, you'll still need the correct content/type header using the STPSRV_HEADER function, but this should get you started.

This SGF paper http://www2.sas.com/proceedings/forum2008/024-2008.pdf
talks about the basics of converting your programs to be stored processes -- it doesn't talk about STPSRV_HEADER, but it does go through the rest of the process. For more help with your stored process, you might consider contacting Tech Support for help.

cynthia

[deleted_user]

Hi Cynthia,

Thanks for your prompt reply. I have modified code as below but it didn't generate the output in Excel format. Please advise.

*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;

%generate_invalid_date;

%stpend;

[Cynthia_sas]

Hi:
Your stored process should have created an HTML file that conforms to the Microsoft HTML specification (MSHTML). When you use ODS to create a file, you are NEVER creating a "binary" .XLS format file. You are either creating an HTML file or an XML file or a CSV file that Excel can open.

If you use ODS CSV, then you create a CSV file; if you use ODS HTML or ODS MSOFFICE2K then you are creating an HTML file; if you use ODS TAGSETS.EXCELXP, then you are creating a Spreadsheet Markup Language XML file.

The STPSRV_HEADER function is merely telling the operating system, what application should be used to view the content that is coming down the HTTP pipeline from the execution of the stored process.

When I run either of these stored processes on my system, using the SAS Information Delivery Portal, my operating system (Windows XP) does successfully launch Excel:

Stored Process Version 1:
[pre]
*ProcessBody;
data _null_;
rc = stpsrv_header('Content-type',"application/vnd.ms-excel");
rc = stpsrv_header('Content-disposition',"attachment; filename=ex1.xls");
run;

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

%stpbegin;

proc print data=sashelp.class noobs;
title 'test html to Excel';
run;

%stpend;
[/pre]

Stored Process Version 2:
[pre]

data _null_;
rc = stpsrv_header('Content-type','application/vnd.ms-excel');
rc = stpsrv_header('Content-disposition','attachment; filename=ex2.xls');
run;
ods listing close;


ods tagsets.msoffice2k body=_webout style=sasweb;

proc print data=sashelp.class noobs;
title 'test html _webout to Excel';
run;


ods tagsets.msoffice2k close;
[/pre]

When I tested these 2 stored processes, BOTH of them successfully executed my stored process from the SAS Info Delivery Portal and my system launched Excel.

Can you test something more similar to my code instead of using your Macro program? If something simple like this does not launch Excel, then it's either that you are receiving the results on a machine that does not have Excel (like Unix or Linux) or there is something else wrong....perhaps a popup blocker that is blocking the "download/open" message that pops up or something else... which would mean a call to Tech Support.

cynthia

[deleted_user]

Dear Cynthia,

I have encountered this error (ERROR: No logical assign for filename _WEBOUT) when I performed both version of stored process example. Appreciate if you could assist. Thanks.

[Cynthia_sas]

Hi:
I can only think of a few things.
-- you are trying to run/test this code from a local SAS session -- where there is no "connection" or plumbing for the BI Platform -- so you get the error message for _WEBOUT -- once you have converted your program to be a stored process with the STPSRV_HEADER and %STPBEGIN/%STPEND, you can no longer submit the program from your single user session -- you have to put the code in the source code repository and register the SP with the metadata and then test running the SP from the Portal.

-- you have a FILENAME statement for _WEBOUT in your code (you should NOT, NEVER, NOT try to issue a FILENAME statement for _WEBOUT.
http://support.sas.com/kb/19/063.html

-- you are using PUT statements to _WEBOUT with the FILENAME statement.
(In stored processes, if you are writing to _WEBOUT using PUT statements while ODS has _WEBOUT
open, when you execute the code the PUT statement data might be out of sequence with the data that is
generated by ODS. This problem occurs because both your code and ODS are opening the same fileref at
the same time.
From: http://www2.sas.com/proceedings/forum2007/023-2007.pdf)

This is the point at which you should consider contacting Tech Support for more help. They can gather your configuration and Stored Process information and double check that you are using the correct method and STPSRV_HEADER to launch Excel when the stream comes back from the Stored Process server. They can double check that you are using the Portal the right way or using the Stored Process Web Application.

cynthia

[FrankVernooij]

Hi,

Does anyone know how to put a graph into an Excel-file created with a stored process? For instance a KPI-speedometer:


*ProcessBody;
data _null_;
rc = stpsrv_header('Content-type',"application/vnd.ms-excel");
rc = stpsrv_header('Content-disposition',"attachment; filename=ex1.xls");
run;
%let _odsdest = tagsets.msoffice2k;
%let _odsstyle = sasweb;

%stpbegin;

%LET bol_webout="%sysfunc(pathname(work))\bol.html" BASE="%sysfunc(pathname(work))";
PROC SQL NOPRINT;
SELECT '_webout'
INTO :bol_webout
FROM dictionary.extfiles
WHERE Upcase(fileref) EQ '_WEBOUT';
QUIT;

ODS HTML FILE=&bol_webout STYLE=bol_style;
proc gkpi mode=raised;

goptions vsize= 2.50 in hsize= 2.50 in CBACK=white;

speedometer actual=&mac_actual_1 bounds=(-100 -25 50 100) /
lfont=( h= 20 PT c= cx005aa1)
colors=(cxD06959 cxF1DC63 cx84AF5B )
bfont=( c= cx005aa1)
afont=( h= 30 PT c= cx005aa1);
RUN;QUIT;

%stpend;

[Vince_SAS]

I'm pretty sure that with MSOffice2K you only get a reference to the image file; it is not embedded in the document.

If you have a Web server or other network-accessible drive, you can alter your SAS code to store the image there, and then make sure that the URL in the HTML file is correct. This is not an optimal solution because you must maintain the generated image files.

Vince DelGobbo
SAS R&D