SAS Stored Process in AMO (Excel) - Sending Data To Multiple Worksheets

[SterlingParamore]

(note: I searched the archives which made it sound like this couldn't be done, but the latest discussion I found was from 2008, so maybe something changed?)

I have a stored process that I need to run in Excel (via AMO) and create multiple tabs of formatted data. This seems possible, because when I run a stored process on the logical workspace server through AMO Excel and produce both ODS output and write a dataset to SASUSER, the ODS output gets put on one worksheet, and the dataset gets output to another worksheet. Is there anyway to send ODS output to multiple sheets? (on another note, is there a way to send work datasets to multiple sheets?)

I'm using 4.2 (but will soon upgrade to 4.3).

For example,

ods html (id=ws1) ;
proc print data = SASHELP.Class;
run;
ods html(id=ws1) close;


ods html (id=ws2) ;
proc print data = SASHELP.Cars;
run;
ods html(id=ws2) close;

[Cynthia_sas]

Hi:
When you use stored processes, you are essentially creating output that comes from a server (the stored process or workspace server) to a client application (such as Excel) -- what comes back is usually a "stream" of data, and as such, the "stream" looks to Excel (or the user sitting in front of Excel) to tell the application where to put the stored process results.

It was my understanding that a single stored process can ONLY populate 1 place when the results come back -- a new worksheet in the existing workbook or a new worksheet in a new workbook (whatever is on the Excel popup window that comes up when you submit a stored process).

And, the same is true of Word, for example, when you run a stored process -- your choices are to put the results in the current open document or to start a new document. You don't have a choice in Word to put results from 1 stored process into 2 separate documents simultaneously.

As far as I know (and you might want to verify this with Tech Support), you can only create multi-sheet workbooks with stored processes that
1) produce streaming results
2) use an ODS destination, such as TAGSETS.EXCELXP, that create multi-sheet workbooks and
3) are submitted using the Information Delivery Portal or the Stored Process Web App -- where you can have control over the streaming HTTP content-type header that will be used by the browser to launch Excel as a "helper" app

If you had a SP that allowed users to select the dataset (either SASHELP.CLASS or SASHELP.CARS) and you wanted to create multi-sheet workbooks using Excel and AMO, your users would have to run the SP one time and pick SASHELP.CLASS and return results to one sheet in the existing workbook or a new workbook and then run the SP a second time, pick SASHELP.CARS the second time and then in the popup window for the SP execution, select that the results should populate a new worksheet in the existing workbook.

I don't know any other way to use AMO and have the SP create multiple tabs.

cynthia

[SterlingParamore]

Cynthia,

Thanks for your response. So, it sounds hopeless. However, I'm still stuck on how multiple tabs are created when the following stored process is run on the workspace server through AMO:

%STPBEGIN;

data SASUSER.cars;
set SASHELP.cars;
run;

data SASUSER.class;
set SASHELP.class;
run;

%STPEND;


This stored process creates three tabs: one that is blank, one called SASUSER.CARS, and one called SASUSER.CLASS. How/why is Excel reading these datasets? I've examined the package results and all I see is main.html and the PackageMetaData binary file.

[Cynthia_sas]

Hi:
I don't actually know how your program is working to create multiple tabs. As far as a "regular" ODS job, if I did this inside a BASE SAS program:
[pre]
82 ods html file='xxx.html';
NOTE: Writing HTML Body file: xxx.html
83
84 data SASUSER.cars;
85 set SASHELP.cars;
86 run;

NOTE: There were 428 observations read from the data set SASHELP.CARS.
NOTE: The data set SASUSER.CARS has 428 observations and 15 variables.
NOTE: DATA statement used (Total process time):
real time 0.06 seconds
cpu time 0.00 seconds


87
88 data SASUSER.class;
89 set SASHELP.class;
90 run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set SASUSER.CLASS has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.00 seconds


91
92 ods html close;
[/pre]

Then the file "xxx.html", although it -is- created, it is an empty file. Well, almost empty. The HTML file has a long <STYLE> section and then at the end of the HEAD section there is only this:
[pre]
</head>
<body onload="startup()" onunload="shutdown()" class="Body">

<script language="javascript" type="text/javascript">
<!--
var _info = navigator.userAgent
var _ie = (_info.indexOf("MSIE") > 0
&& _info.indexOf("Win") > 0
&& _info.indexOf("Windows 3.1") < 0);

//-->
</script>

</body>
</html>
[/pre]

Essentially, there is nothing between the <BODY> and </BODY> tags. Since a SP using %STPBEGIN is essentially supposed to capture what would be between the BODY tags, I am baffled by the behavior you describe.

The fact that you see multiple tabs in Excel when submitting the SP with AMO tells me that behind the scenes, the server used for the SP creates a package of binary information. AMO, behind the scenes must be taking that package and doing some form of transformation -- perhaps the only kind of transformation it can do is show you the data files.

Normally, a SP "returns" something that you can see -- as with my BASE SAS program -- 2 data step programs do not create any report output which can be shown in an HTML output file. Perhaps %STPBEGIN detects the fact that there is no "regular" ODS output and does something different.

It would never have occurred to me to even write a stored process such as this one. Normally, if I were writing an SP that created a data file, I would pick NONE or NO result type (NO PACKAGE/NO STREAMING) for the SP execution/result settings.

cynthia

[SterlingParamore]

It wouldn't have occurred to me either. I stumbled upon it by accident some time ago when I had a SP that created some ODS output, but also updated a table that was registered with the metadata server. When the SP was run through AMO, the table would show up as an extra tab (turns out this is because I have the option "Open output data automatically" selected in my AMO options). This was behavior that I definitely did NOT want to happen and had to write a hack to fix it. However, I still wonder if there's a way to make those tables show up as tabs even when they're not registered.

[Cynthia_sas]

There's a difference in my mind between a DATA table (such as you were creating in your DATA step program), and ODS output. Typical stored process results are reports and not data tables.

In the Excel/SAS comparison world, a Workbook is the equivalent of 1 SAS library. A SAS library can contain multiple SAS datasets or SAS tables. Within a Workbook, each tab is the rough equivalent of a SAS dataset or table. This is how you read and write data in binary Excel form when you use PROC EXPORT or the SAS Excel LIBNAME engine (without having the BI Platform).

A "report" on the other hand is NOT a SAS dataset. A "report" is a report -- it can be a report composed of multiple "pages" or multiple report tables that would span multiple paper pages, if printed.

In the ODS HTML world, for example, even if I run 10 different procedures within 1 ODS "sandwich" (ODS HTML file=.../ODS HTML CLOSE), if I take ALL the defaults with ODS, all 10 procedure outputs go into 1 HTML file....which would show up in 1 browser screen.

Your stored process results are more like ODS HTML report output -- where 1 stored process can create 1 thing -- 1 sheet in the existing workbook or 1 sheet in a new workbook or new stored process results can be appended to an existing sheet in an existing workbook.

You asked:
However, I still wonder if there's a way to make those tables show up as tabs even when they're not registered.

And my thinking is, it depends on what you mean by TABLES -- do you mean SAS dataset/TABLES or do you mean stored process report output as filtered through ODS. This might be a question for Tech Support, but I believe that fundamentally, you are wanting SP report output to act like dataset tables that are registered in the metadata and since they are not the same thing, they weren't ever coded to act as though they are the same thing. An ODS report is not the same as a dataset. It might be a report -based on- a dataset (such as you get with PROC PRINT). For another example, a dataset created by PROC TABULATE will not have the same (nice and desirable) hierarchical and nested structure as a report output created by ODS and PROC TABULATE (such as you would create with a stored process).

cynthia

[SterlingParamore]

Cynthia,

I do understand the difference between a report and a table. But the fact remains: when I have a stored process run through Excel AMO that creates/modifies a table in SASUSER or one that is registered with the metadata server, the TABLE shows up as an Excel tab.

Perhaps this behavior is just a quirk of my software versions? (Excel 2007 and AMO 4.2 - and I have the display output option checked as discussed in a prior e-mail) Have you been able to reproduce the issue I've observed?

Thanks for you help and interest,
Sterling

[Cynthia_sas]

No, I do not have a ready image at those levels to test on. If you want to see whether the behavior is reproducible on software/install that matches yours, you should check with Tech Support.

cynthia