Export STP report to Excel?

[deleted_user]

Hello. I have a stored Proc report that has multiple output types (HTML, PDF, CSV)

I would to export the HTML report straight to Excel as we do not have the AMO client installed on our users machine.

I've seen many examples where the code is put before the %STPBEGIN:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
data _null_;
rc = stpsrv_header('Content-type','application/vnd.ms-excel;');
rc = stpsrv_header('Content-disposition','attachment; filename=temp.xls');
run;
%let _odsdest=tagsets.msoffice2k;
%let _odsstyle=sasweb;
~~~~~~~~~~~~~~~~~~~~~~
and this is fine if all my reports were to come out in Excel, but how can I control the output so that if the user select PDF it will be PDF? I've tried putting an IF statement, but I get an error about putting the IF in open code.

TIA
Patrick

%STPBEGIN;

[Cynthia_sas]

Hi....
Some clarification...first of all, a stored process is executing SAS code on the server. It CAN create files (using permanent packages) either in a webDAV server or on the server file system. Typically, you do not consider a stored process to be "exporting" anything. The SP is returning results...typically, those results are tailored to the client that requested the SP to be executed.

There is a HUGE difference between running the SP with the SPWA and running the SP from within Web Report Studio, for example. Web Report Studio will not "accept" PDF or HTML results from a Stored Process. Web Report Studio only wants to accept SASReport format output from a stored process. Using the Portal and/or the Stored Process Web Application, you could generate either HTML, or CSV or PDF results and, assuming you had the correct STPSRV_HEADER, the SPWA would send your results back to the requesting browser and the browser would ask the operating system to use the application specified in the content-type header.

If you do not have the SAS Add-in for Microsoft Office, then, how are you going to execute your stored process? I ask, because your only choices would be to use SAS Enterprise Guide, SAS Web Report Studio, the SAS Information Delivery Portal or using the Stored Process Web Application (SPWA) or with a custom front end that you write yourself, perhaps using JavaBeans or a JSP to execute the stored process.

Assuming that you are executing your SP from the SPWA or the Portal, then you can issue the correct &_ODSDEST and/or STPSRV_HEADER change within a SAS Macro program.

If you look at this paper from the 2007 SAS Global Forum,
http://www2.sas.com/proceedings/forum2007/021-2007.pdf
I had a single starter program that produced 3 outputs and I wanted to convert it to a stored process. In the paper, my recommendation was that you actually make 3 separate stored processes, one for each destination (because of the variability of what kind of results would be "accepted" by each client application).

But, if you REALLY wanted 1 SP that would produce 1 type of output at a time for each request, then I have a macro program called %SETOPT that shows how to check what the user selected and issues the appropriate changes BEFORE
%STPBEGIN starts up. (shown in the paper as DEMO5_MACRO.SAS)

The only difference between what you want to do and what I'm doing in my program is that you would put your whole data _null_ program inside the appropriate %IF/%END section of code in the macro program. (shown on page 13 of the paper).

The final flow of your SP will be something like this:

[pre]
**** start of SP ****;
%macro setopt;
....use %IF statements here with DATA _NULL_ code inside each %IF/%END
....also set correct &_ODSDEST in the %IF section;
%mend setopt;

*ProcessBody;
%stpbegin; /* <------note that %stpbegin is all the way DOWN here */
%setopt; /* above is only the macro definition HERE is the macro pgm invocation */
...more code...;
%stpend;
*** end of SP ***;
[/pre]

The other reason for having 1 SP for each result type is that if you make 3 SPs to start, then you know EXACTLY what the correct DATA _NULL_ for STPSRV_HEADER is and what the correct options are for each result type that you want the stored process to return. This practice falls under my general rule of thumb that before you dive into macro coding, you have to have a working SAS program first.


cynthia

[deleted_user]

Thanks Cynthia! Your are truly a wealth of information. I'm running the SP from either the Portal or SPWA.
I ran it, and it came back in Excel!
However, I do question the mode of coding the %macro setopt BEFORE the *ProcessBody. We've learn, the hard way, that if you use EG to modify the SP code or add parameters, EG will erase and recode anything above the *ProcessBody line. You would then lose this nice set of code you have provided.

Anyhow, this works great and once again than k you.
Thanks again!
--now on to my next question! 🙂

[Cynthia_sas]

Hi, Patrick:
That EG behavior is why I NEVER use EG to edit an SP to which I have added custom macro code. I will always edit the SP code outside of EG and then, if I need to, reregister or add new parms via the SAS Mgt Console screens.
There are also choices that you can make in the SP Wizard to tell it NOT to add any additional SP macro code.
My only problem with your approach is that %stpbegin is a macro call. I learned a long time ago (in a galaxy far, far away) that it is not generally a good idea to define a macro program while another macro was in control of the environment. It's OK to have nested macro calls:
[pre]
*ProcessBody;
%stpbegin;
%setopt;
%stpend;
[/pre]

And the fix for where the macro definition should -really- go is inside an autocall macro location on the BI Application Server Context -- that way it's not in your code, EG won't tamper with the call inside the %stpbegin and you do not have the macro definition in your SP. Of course that means that you probably need to do some extensive testing and have a lot of experience with macro programming in order to effectively debug your macro program before you put it in an autocall library.

Anyway, down in the Lev1 directory on your installation, you should find a SASEnvironment directory and underneath that directory, you should also find a pre-defined location for user-defined formats. There should also be a library for SAS Macro code that you are going to autocall and SAS code snippets that you're going to %include. So in a true production environment, I would not even have the macro definition in my SP.

cynthia

[deleted_user]

I'm glad to hear that you stopped using EG to edit a SP. We started doing the same thing after we figured out the overwrite issue. I like the idea of putting the macro inside the autocall library. I'll have to give that a shot.
Thanks again!