Your SAS programs, embedded in web apps and elsewhere

Excel and Stored Process

Reply
Occasional Contributor
Posts: 5

Excel and Stored Process

I have to call a stored procedure from Excel.
The stored procedure generates multiple tables SAS.
I hope that when I call the stored procedure, each table opens in a different Sheet.
For the moment, all the tables are created in the same Sheet.

Thanks
SAS Super FREQ
Posts: 8,862

Re: Excel and Stored Process

Posted in reply to parametreSEG
Hi:
At the present time, your stored process (SP) populates only one sheet. You cannot populate multiple sheets from a single stored process using the SAS Add-in for Microsoft Office (SAS-->Reports-->Select your Folder -->Select and Run your SP)

There is a way to generate a Spreadsheet Markup Language XML file (The Microsoft XML description of a workbook with multiple worksheets.) with SAS. In Base SAS, you use the TAGSETS.EXCELXP destination to create Microsoft-specific XML that can contain multiple worksheets in one workbook.

However, the SAS Add-in for Microsoft Office does not "recognize" this type of XML from a Stored Process. Your only choices for SP output when using the Add-in are SASReport XML, HTML or CSV. When you run an SP using the Add-in, the user gets a popup window asking them where they want the SP output to be placed. The design of the Add-in allows the user to make that choice -- and the SASReport XML or HTML or CSV results from the SP are placed according to that choice. So if you want the users to utilize the SAS Add-in for Microsoft Office to run the SP, then they'd have to run a separate SP or a make different choices for the same SP on a second, third, fourth worksheet using the Add-in to get a multi-sheet workbook.

If you use the Stored Process Web Application or write your ExcelXP XML results to a permanent package that can be retrieved by the user, then you could use TAGSETS.EXCELXP to create a multi-sheet workbook from your stored process.

For more help using the Stored Process Web Application or creating permanent packages and on overriding the default value for _ODSDEST in order to generate ExcelXP output (multi-sheet), your best bet for help is to read the Stored Process Web Application documentation or to contact Tech Support for help.

cynthia
Ask a Question
Discussion stats
  • 1 reply
  • 226 views
  • 0 likes
  • 2 in conversation