The SAS Output Delivery System and reporting techniques

Stored procedure to Excel

Reply
Frequent Contributor
Posts: 115

Stored procedure to Excel

Hello,
I am trying to have a stored procedure in IDP download an Excel report to the clients machine. This spreadsheet includes sgplot graphs.

The stored procedure uses these headers :


data _null_;
rc = stpsrv_header('Content-type','application/vnd.ms-excel');
rc = stpsrv_header('Content-disposition',"inline");
run;

ods listing close;
ods markup body=_webout tagset=tagsets.msoffice2k;

/*proc sgplot here */
/*proc report here*/
ods markup close;


It creates the sheet with the proc report info but the charts just have place holders.


Anyone run into this?
SAS Super FREQ
Posts: 8,743

Re: Stored procedure to Excel

Hi:
I figure you're talking about SAS stored processes, not stored procedures, such as those used by data base systems .

You did not say how your stored process was defined in the metadata. I assume that you have defined the output type or output results to be "streaming" instead of "package". If you look at the HTML stream that is created by your stored process, you should see an IMG tag. Usually, that IMG tag refers to a physical location on the server where the program was executed. If you defined the stored process to use package output (instead of streaming), then the HTML results and the image file would come down in one zip archive and would be "unpacked" at the client end.

You might actually wish to work with Tech Support on this question, because there are differences in how stored process result types needed to be defined in different versions of SAS. Some versions of SAS used temporary packages and permanent packages; instead of just one package type. If you have defined "package" as the output type and you are not seeing results, then you will definitely need to work with Tech Support.

cynthia
Frequent Contributor
Posts: 115

Re: Stored procedure to Excel

Actually, it is stored as streaming.
I'll try changing to package.

Thx.
SAS Super FREQ
Posts: 8,743

Re: Stored procedure to Excel

If you do that, you may need to alter the content-type header in the STPSRV_HEADER function. I'm just curious why you're not using the SAS Add-in for Microsoft Office for this. It knows how to unpack the stored process results if you invoke the SP directly via the Add-in. You might not even need the STPSRV_HEADER function at all, either with the IDP or if you used the Add-in and the package results.

cynthia
Frequent Contributor
Posts: 115

Re: Stored procedure to Excel

Good question.
I already have the report on the Information Delivery Portal. It's a dashboard which allows users to drill into different detail/categories, generating charts and summaries. I want them to be able to save a snapshot of the detail they've created into Excel.
Frequent Contributor
Posts: 115

Re: Stored procedure to Excel

I tried the streaming.
But now i get an error.
You mentioned I need to change the header functions?
SAS Super FREQ
Posts: 8,743

Re: Stored procedure to Excel

I'm not sure what the correct MIME type header is for the SAS package file (it's extension is usually .SPK). You might want to check with Tech Support. I'm not sure that Excel will open the SPK file. That's why I mentioned the Add-in. If you have an SP that creates package output and you use the SAS Add-in to execute the SP, the Add-in knows how to "unpack" the package and insert the results into an Excel sheet. If you make package output, you are no longer creating just a single stream of HTML -- you have HTML content and IMAGE content in the package -- that content has to be "unpacked" in order to be opened by either a browser (since it's HTML) or by Excel (which can read HTML). In SAS 9.1.3, the SAS Package Reader was an "unpack" tool that could be used to open a SAS package file: http://support.sas.com/rnd/itech/doc9/dev_guide/reader/index.html but, I also believe that you might be able to use 3rd party unzip utilities also. Things have changed a bit with packages between SAS 9.1.3 and SAS 9.2, so this is a better question for Tech Support.

If you bypass the Add-in (such as you are doing with your SP submitted via the Portal) I'm not sure what receiving application should be on the other end of the stream,

cynthia
Contributor
Posts: 33

Re: Stored procedure to Excel

I, too, am having trouble streaming graphical output via IDP to Excel. I can easily stream the table to an Excel workbook, but the graph does not follow.

What are the steps to include in my stored procedure that would allow a graph to be generated via IDP and streamed to Excel from the IDP?

Thank you

Nathan Och

SAS Super FREQ
Posts: 8,743

Re: Stored procedure to Excel

Hi:

  As I explained above, you may need to change the content-type header using the STPSRV_HEADER function in your stored process. You may need to have your Stored Process create package output. You may need to switch the server you use. Sometimes, you might need to use special reserved locations and/or stored process facilities like _SESSIONID or _TMPCAT or _REPLAY. The bottom line is that if your _ODSDEST is MSOFFICE2K or HTML, then you can send tables and graphs to Excel. However, if your value for _ODSDEST is TAGSETS.EXCELXP, then graphs are not supported in that version of Office Spreadsheet Markup Language XML -- by Microsoft design. So this is a question for Tech Support because what you want to do and how you do it will depend on many different factors.

cynthia

Ask a Question
Discussion stats
  • 8 replies
  • 723 views
  • 0 likes
  • 3 in conversation