Run STP from Excel and put results into different sheets

[deleted_user]

Hi guys
I have 3 tables, and I need Stored Process, which should be runed in Excel (using plug-in) and put these 3 tables into 3 different sheets:

Now my code looks like this:

%stpbegin;
proc sql;
create table A as select ...
quit;
proc sql;
create table B as select ...
quit;
proc sql;
create table C as select ...
quit;

title;
footnote;
ods listing close;
options missing=0;
ods tagsets.excelxp file='c:\temp\test.xls';
proc print data=work.A;
run;

ods tagsets.excelXP ;
proc print data=work.B;
run;

ods tagsets.excelXP ;
proc print data=work.C;
run;
ods _all_ close;
%stpend;

This code works in SASBase and creates file c:\temp\test.xls with 3 sheets, but when I try to run it in Excel it says that STP was compleated succesfully, but with no visual content.

Please help me to find another solution, probably some corrections into this one.
Thank you.

[Cynthia_sas]

Hi:
The SAS Add-In for Microsoft Office when used with Excel, only allows SASReport XML, HTML and CSV results. TAGSETS.EXCELXP is NOT a valid result type if you are using the SAS Add-In for Microsoft Office. Also, with the code you show below, it seems that you are trying to give a definite name to the file that is created from this stored process. That is not really valid syntax when you are using
%stpbegin.

If you search for previous forum postings on the subject of Excel, you will find that in order to use TAGSETS.EXCELXP in a stored process, you can only use that destination with applications that you submit from the SAS Information Delivery Portal or submit using the Stored Process Web Application (SPWA). Generally, with stored processes, it is not appropriate to use this syntax:[pre]
ods tagsets.excelxp file='c:\temp\test.xls';
proc print data=work.A;
run;

[/pre]

within %stpbegin/%stpend -- because those special macro calls set up the environment that is appropriate to the client application that called the stored process. For example, this stored process would not run in Excel or PowerPoint or Web Report Studio...none of those client applications accept TAGSETS.EXCELXP output.

Generally, if you are going to use TAGSETS.EXCELXP, it would be through the Portal or the SPWA. In that case, you do NOT use a hard-coded FILE= option. Those client applications send STREAMING output from the server to the client browser (or client system) using the HTTP protocol. If you wanted to launch Excel when the SP ran, then you would make sure that your SP sent the right HTTP content-type header (using the STPSRV_HEADER function). Previous forum postings have shown how to use the STPSRV_HEADER function with &_ODSDEST and with _WEBOUT.

cynthia

[deleted_user]

Yes, I understood, that html should be, but it still doesn't work.
Seems it's not possible 😞

[deleted_user]

I did that on EG: Table -> 3 queries -> 3 Data Lists and it works, but everything displays on one sheet. I can't output on different sheets. Have any ideas?

[Cynthia_sas]

Hi:
If you create an HTML file with multiple queries or multiple procedures' output and then open that HTML file in Excel, by design (Microsoft), a single HTML file represents a single Worksheet in an Excel Workbook. That makes sense, actually, (as inconvenient as it is) because a single HTML page on the web can be composed of multiple tables ... and just like the browser shows you all the tables that are in a single HTML page, Excel shows you all the tables in one Excel Worksheet in the Workbook....when you create and open HTML files with Excel. Rather than redesign the fundamental workings of HTML, Microsoft came up with Spreadsheet Markup Language XML -- which allows an XML description of multiple sheets to be rendered by Excel as you would expect.

That's one of the reasons that Microsoft came up with the Spreadsheet Markup Language specification -- because folks wanted a way to open a file (XML) and have Excel render the XML file as multiple sheets in one workbook. Since SAS can create CSV and HTML and Spreadsheet ML, you have the ability to decide which kind of file you want to create with SAS and ODS. Once the BI Platform and the SAS Add-In come into the mix, then you must play by the rules of the SAS Add-In, which can only receive CSV, HTML or SASReport XML results from an SP.

SASReport XML is NOT the same thing as Microsoft's Spreadsheet ML. SASReport XML does not have a way to automatically create multiple worksheets. That's why you must use the SPWA or the Portal to execute SPs that might use TAGSETS.EXCELXP -- web-based methods of executing a SP are not bound to fixed result types because you can precede your streaming output with a content-type header that informs the client system what application should be used to open and render the content.

If you remember how the SAS Add-In for Microsoft Office works, when you run a SP in Excel, it prompts you for where you want to insert the SP results -- in the current worksheet or in a new sheet or in a new workbook. This is part of the internal workings of the Add-In. In the SP class, we recommend that you either run 3 SPs -- each one to populate a separate sheet or that you run the same SP 3 times ( possibly with different parameter choices each time) and then at the prompt, choose to put each SP results in a different sheet.

If you decide that you want/need the features of TAGSETS.EXCELXP, such as creating multiple worksheets in one workbook automatically, using a Stored Process, then you cannot execute that SP via the SAS Add-In. The SAS Add-In for Microsoft Office, when used in Excel, does not "accept" or "render" TAGSETS.EXCELXP form of XML.

To use TAGSETS.EXCELXP, you would have to execute your SP through the Portal or the SPWA (Stored Process Web Application). Here are some previous forum postings on this topic:
http://support.sas.com/forums/thread.jspa?messageID=6617᧙
http://support.sas.com/forums/thread.jspa?messageID=12735ㆿ
http://support.sas.com/forums/thread.jspa?messageID=9596╼
http://support.sas.com/forums/thread.jspa?messageID=11407ⲏ

cynthia

[deleted_user]

Hi Cynthia
Do you mean this way http://support.sas.com/rnd/base/ods/excel/multisheet_excel_post.ppt ?
As I understand, it should allow me to run STP in Excel (using add-in) and display data in different sheets?

[Cynthia_sas]

Hi:
No, that is not what I meant. I -have- used the technique shown in that paper to create a multi-sheet workbook in BASE SAS, but not as a Stored Process. So I can't comment on whether it would work or not.

I suspect that it won't work because it requires that you have a specific _folder_ structure in which to store the multiple HTML files. That folder structure would live on the BI Platform server machine and you'd need to have write access to the folder in order to create the individual HTML files. The other reason I'm not sure that technique will work is because the SAS Add-In wants you to tell it how the results should populate inside Excel -- so IF you could make this technique work, then I'm not sure how the Add-In would handle or if it would be able to handle something like a linked set of HTML files.

If you want to explore how to create a multi sheet workbook from a stored process, then you might want to work with SAS Tech Support. Maybe you could explore creating a permanent package from ODS -- which contained all the related files or contained TAGSETS.EXCELXP output.

I still think the easiest way to use TAGSETS.EXCELXP to create a multi-sheet workbook is to execute your stored process that uses TAGSETS.EXCELXP using the Stored Process Web Application or the Portal.

cynthia