suppressing output from a SP ran via office add-in

[deleted_user]

I'm using Windows XP / SAS EG 4.1 / Office 2003

Is it possible to suppress an output table generated by a proc tabulate within a stored process?

I have somthing like the following -

proc tabulate;
......
run;

proc gbarline;
.....
run;

proc tabulate;
......
run;

The first proc tabulate is required to generate another sas table, but i don't want the table output to be displayed. I have tried the following -

ods listing close;
proc tabulate;
......
run;
ods listing;

proc gbarline;
.....
run;

proc tabulate;
......
run;

but i still get 3 objects into excel when I run the stored process, instead of the desired 2 objects. Any ideas?

[deleted_user]

I've managed to work around this by using a proc summary rather than the proc tabulate, but I'm still curious as to why the ods listing close doesn't stop the table from appearing in the output.

Cheers,
Dave.

[Cynthia_sas]

Hi:
ODS LISTING CLOSE; would work if you were in a regular SAS session to suppress the table from PROC TABULATE. However, in a Stored Process (SP), you probably also had this (or something like it):
[pre]
%stpbegin;
tabulate
gbarline
gbarline
%stpend;
[/pre]


Which means that the output was being captured by the open destination for the stored process (either HTML, CSV or SASReport XML) depending on what kind of results your SP was going to return to the client app. In Excel, you can only generate ODS CSV, ODS HTML or SASReport XML Results from a SP.

The way to make your SP most flexible is to NOT try to control the destination that the SP will return. That way a user could decide to get CSV results from one SP and HTML results from another SP by changing the result type under the SAS Add-in menu in the Microsoft product. From that standpoint, it's hard to know WHICH destination to close in your SP code, if you allow the user to choose what kind of results they will get from the SP. (there is a possible solution...)

However, back to ODS LISTING CLOSE, closing the LISTING destination was never going to suppress your output from appearing in Excel because the LISTING destination is never involved in SPs -- LISTING is not even a possible choice for returning SP results to Excel.

One thing you could try (although it seems a moot point since you've moved onto PROC SUMMARY) is something like this in your SP:

[pre]
%stpbegin;

ODS &_ODSDEST CLOSE;
proc tabulate step;
ODS &_ODSDEST;

gbarline
gbarline
%stpend;
[/pre]


Using the reserved macro variable reference for the destination should allow you to selectively close and reopen the destination being used for whichever destination is used for the SP.

Note that if you use the above syntax in a regular SAS session for testing, you will probably get a macro error message because the value of &_ODSDEST is not assigned in a regular session -- only in a session that's under the control of the Workspace server or the Stored Process server.

cynthia

[deleted_user]

Thanks Cynthia, now I understand.