topic Re: Running ExcelXp tagsets in Stored Processes in Developers

Running ExcelXp tagsets in Stored Processes

saspert — Mon, 14 Feb 2011 19:07:31 GMT

Hello,
I have a stored process that does the following-
1. Builds a javascript page to get input parameters from user
2. Runs excelxp tagsets to generate a report based on the parameters given by the user.
However, when I run the stored process from the stored process URL, I dont get the excel report back. It does work independently of the stored process though.

I am thinking maybe one of my options maybe incorrect.

%macro output;
options orientation=landscape leftmargin=0.5in rightmargin=0.5in topmargin=0.5in bottommargin=0.5in ls=160 ps=50 mprint symbolgen;
%global format _odsdest _odsstyle _odsoptions;
%macro header;
%IF &format = %THEN
%DO;
%LET _ODSDEST =tagsets.excelXP ;
%LET _ODSSTYLE = ;
%LET _ODSSTYLESHEET = ;
%LET _ODSOPTIONS = options=(doc="help");
DATA _NULL_;
rc = STPSRV_HEADER('Content-type', 'application/vnd.ms-excel');
rc = STPSRV_HEADER('Content-disposition', 'attachment; filename=Boeing_HA_Completion_Weekly.xls');
RUN;
%END;

%mend header;

%header;
%stpbegin;

ods _all_ close;

%let source=/sasdev/optum/rpt/phi/projects/eHP/Release2/Development_Code/tagset_codes/;

* Library to store styles and tagsets;
ods path reset;
libname libtmpl "&source";

* Set the ODS search path for styles and tagsets;
ods path work.tmplmst(update) /*libtmpl.tmplmst(update) changed to work.tmplmst(update)*/
sashelp.tmplmst(read);

%include "/sasdev/optum/rpt/phi/projects/eHP/Release2/Development_Code/excel_120/excltags.sas";

proc format;
value $relation '000'='Employee'
'001'='Spouse / Domestic Partner';
run;

ods tagsets.ExcelXP file="sample_report_v7.3_sql.xls" path="&source." style=sasweb options(doc="help");

*Create Title Page;
ods tagsets.ExcelXp options(sheet_name='Title Page'
sheet_interval='none' absolute_column_width='9');
ods path reset;
ods tagsets.ExcelXp close;
libname libtmpl clear;

%stpend;

data _null_;
file _webout;
put 'center a
href="http//.../ha_completion_weekly"
img src="http//.../SAS%20Store%20Process%20Images/Prompt.bmp" border="0" /
/a/center';
run;

%mend output; I have removed the HTML tags for "center""a"

Message was edited by: saspert

Re: Running ExcelXp tagsets in Stored Processes

Vince_SAS — Mon, 14 Feb 2011 20:33:08 GMT

What does happen when you run the code as a stored process?

Try running the code with OPTIONS MPRINT turned on at the top. Your ODS statements may be conflicting with those that are being generated by STPBEGIN.

You might also want to try with something simple, and then build upon it:
[pre]
*ProcessBody;
options mprint;

ods path work.tmplmst(update) sashelp.tmplmst(read);

%include '/sasdev/optum/rpt/phi/projects/eHP/Release2/Development_Code/excel_120/excltags.sas';

%let _ODSDEST=tagsets.ExcelXP;

%let RV=%sysfunc(stpsrv_header(Content-type, application/vnd.ms-excel));
%let RV=%sysfunc(stpsrv_header(Content-disposition, attachment; filename="Boeing_HA_Completion_Weekly.xls"));

%STPBEGIN;
proc print data=sashelp.class; run; quit;
%STPEND;
[/pre]

Vince DelGobbo
SAS R&D

Re: Running ExcelXp tagsets in Stored Processes

Cynthia_sas — Mon, 14 Feb 2011 21:00:42 GMT

Hi:
In addition to Vince's suggestions (to simplify your stored process as a means of debugging), I want to mention that many of these system options, specified at the top of your code are inappropriate for TAGSETS.EXCELXP output:
[pre]
options orientation=landscape leftmargin=0.5in rightmargin=0.5in
topmargin=0.5in bottommargin=0.5in ls=160 ps=50 mprint symbolgen;
[/pre]

Several of these system options are LISTING-destination only options, such as LINESIZE and PAGESIZE and other options, such as ORIENTATION, and the MARGIN options only work for "paged" destinations like RTF and PDF.

When you are using TAGSETS.EXCELXP for output generation, attributes like orientation and print settings are controlled by suboptions specified like this:
[pre]
ods tagsets.excelxp ...... style=sasweb
options(suboption-list);

an example of a suboption list would be:

ods tagsets.excelxp ...... style=sasweb
options(doc='Help' embedded_titles='Yes' orientation='landscape' zoom='150');
[/pre]

ExcelXP will not use LINESIZE and PAGESIZE and will not use the system option settings for print settings.

This user-group paper discusses more of the print-related sub-options used with TAGSETS.EXCELXP:
http://www.nesug.org/proceedings/nesug08/ap/ap06.pdf

cynthia

Re: Running ExcelXp tagsets in Stored Processes

saspert — Mon, 14 Feb 2011 22:18:10 GMT

Hi Vince,
Thanks for the tip. So I moved the ODS options to run prior to the ExcelXp ODS macro variables and %stp macro variables. It seems to work although partially. Now, I am getting the output directly in the HTML window rather than the excel file.
I made sure I had both the stpsrv statements in there.

Also, I am turning on the mprint and symbolgen options but the client environment does not save logs and I am not sure how to read them.

Thanks,

Update - So I removed the unnecessary ods path reset statements, the ods excelxp close statements, the macro to check for the format (excel or otherwise) and set the _ODS options to this -

%LET _ODSDEST =tagsets.excelXP ;
%LET _ODSSTYLE = ;
%LET _ODSSTYLESHEET = ;
%LET _ODSOPTIONS = ;
DATA _NULL_;
rc = STPSRV_HEADER('Content-type', 'application/vnd.ms-excel');
rc = STPSRV_HEADER('Content-disposition', 'attachment; filename=Boeing_HA_Completion_Weekly.xls');

RUN;

Message was edited by: saspert Now, the webpage is returning the XLS document back to me. But when I open it says problems came up while opening the following with an empty list of items.

So, if I want to have 5 pages with each different ODS options like tab name, column widths and so on, do I have to use ODS tagsets.ExcelXp options(....) or %let
_ODSOPTIONS= statement?

Thanks

Message was edited by: saspert

Re: Running ExcelXp tagsets in Stored Processes

Vince_SAS — Mon, 14 Feb 2011 22:56:51 GMT

To return the SAS log to the Web browser client, use the _DEBUG parameter and set it to "log,trace" (no quotes).

http://support.sas.com/documentation/cdl/en/stpug/61271/HTML/default/viewer.htm#dbgsrvlt.htm#setdebug

Excel-specific options are set as tagset options. Generally you will need to introduce extra ODS statements to set different options for different sheets. Take a look at some of my papers where I cover many scenarios, but be careful to get the syntax correct for the additional ODS statements.

http://www.sas.com/reg/gen/corp/867226?page=Resources

Vince DelGobbo
SAS R&D

Re: Running ExcelXp tagsets in Stored Processes

saspert — Tue, 15 Feb 2011 00:04:25 GMT

Thanks Vince,
I added the _debug option in the place where I have the other _ODS options. But I only get back the XLS file and a blank HTML window. Does the log appear supposed to appear in the HTML window?

I have SAS 9.1.3 and the link seems to be from the 9.2 documentation. Is it available on 9.1.3?

Thanks,

Re: Running ExcelXp tagsets in Stored Processes

saspert — Tue, 15 Feb 2011 00:28:55 GMT

Thank you Cynthia. I am reworking a code that was built by the previous programmer. In the process, I ignored these lines which were meant for PDF destination.

I should make it a point to make a mental note of this. 🙂

Regards

Re: Running ExcelXp tagsets in Stored Processes

Vince_SAS — Tue, 15 Feb 2011 01:39:17 GMT

> Thanks Vince,
> I added the _debug option in the place where I have
> the other _ODS options. But I only get back the XLS
> file and a blank HTML window. Does the log appear
> supposed to appear in the HTML window?
>
> I have SAS 9.1.3 and the link seems to be from the
> 9.2 documentation. Is it available on 9.1.3?
>
> Thanks,

_DEBUG has been around since the mid 1990's as part of the SAS/IntrNet product, and we included it in SAS Stored Processes.

The 9.1.3 documentation is here:

http://support.sas.com/rnd/itech/doc9/dev_guide/stprocess/index.html

If you use the STP Web App to execute the stored process, click "Execution Options" and then select "Show SAS Log" and "Show execution trace".

http://support.sas.com/rnd/itech/doc9/dev_guide/stprocess/stpwebapp.html#execopts

If you are using your own input form, add a field for _DEBUG:
[pre]

[/pre]
Vince DelGobbo
SAS R&D

Re: Running ExcelXp tagsets in Stored Processes

saspert — Tue, 15 Feb 2011 21:11:31 GMT

Hi Vince,
Many thanks for the tip. I was not aware of such options. The stored processes that I am running gives the output
1. Description page (keywords, location, name...)
2. SAS execution options page. I am choosing yes here to turn on the log.
3. Client specific prompt page which needs a few inputs.

#3 is being built by a javascript code which is embedded inside a data _null_/put statements combination. So, I included the html code you gave me to show the log.

Now what happens is that I briefly get the log on the browser window and then it changes back to the http://sas.client.com:8080/SASStoredProcess/do? URL without giving me my Excel output.

I am including the entire log here since I am not sure which parts are most relevant.

>>> Using previous session context
>>> Using username ... in domain DefaultAuth
>>> Repository Foundation
>>> Parameter file

/ebiz/sas/WebSphere61dev/AppServer/installedApps/c1_sas_dev/SASStoredProcess_war.ear/SASStoredProcess.war/

Params.config
>>> Stored Process Input Parameters:
_debug = log,trace
_domain = DefaultAuth
_grafloc = /sasweb/graph
_htcook = JSESSIONID=0000hWT-kIbdQ-xNKsBmeuvbMdd:14sdi9m9g
_htua = Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727;

.NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; InfoPath.1)
_program = //Foundation/BIP Tree/ReportStudio/.../ha_completion_weekly
_repository = Foundation
_reqmeth = POST
_rmtaddr = 10.84.133.14
_rmthost = 10.84.133.14
_srvname = sasdev.uhc.com
_srvport = 8080
_url = /SASStoredProcess/do
_username = ...
_version = Version 9.1.3 (Build 354)
format = Excel
refresh_submit = 1
startdate = 01/01/2011
>>> Servlet Session Data:
Servlet Session ID: hWT-kIbdQ-xNKsBmeuvbMdd
Counter: 18
Creation Time: Tue Feb 15 14:31:05 CST 2011
Last Accessed Time: Tue Feb 15 14:58:05 CST 2011
Servlet Session Timeout: 900
>>> User context
>>> Using PathUrl SBIP://Foundation/BIP Tree/ReportStudio/..../ha_completion_weekly(StoredProcess)
>>> Creating Stored Process Object
>>> Got Stored Process //Foundation/BIP Tree/ReportStudio/.../ha_completion_weekly
Name: ha_completion_weekly
Directory: /sasdev/.../Release2
File Name: ha_completion_weekly.sas
Description: HA Completion Weekly
>>> Setting stored process parameters
>>> Executing stored process object
>>> Getting _webout return stream
>>> Getting return headers
Content-disposition: attachment; filename=HA_Completion_Weekly.xls
Content-type: application/vnd.ms-excel
>>> Opening return data stream
>>> Starting return data read
ioprptsp ioprptsp 2011-02-15T14:57:50 2011-02-15T14:57:50 SAS Institute Inc. http://www.sas.com

9.01.01M3P02022006

Re: Running ExcelXp tagsets in Stored Processes

Vince_SAS — Tue, 15 Feb 2011 22:25:16 GMT

The MIME headers look correct.

Are you running the simple STP code that I posted (PROC PRINT of SASHELP.CLASS)? If not, get that working first, then move to the more complicated case.

Another thing to try is getting rid of the STPSRV calls for the time being. The headers may be affecting the ability to see the SAS log. Also, the XML returned can affect display of the SAS log.

Vince DelGobbo
SAS R&D

Re: Running ExcelXp tagsets in Stored Processes

saspert — Tue, 01 Mar 2011 17:28:16 GMT

Hi Vince,
It took me a while to get back into this task at work. Sorry for the delay. Here is the code I am running -
*ProcessBody;
options mprint;
ods path work.tmplmst(update) sashelp.tmplmst(read);
%include '/sasdev/.../excltags.sas';
%let _ODSDEST=tagsets.ExcelXP;
/*%let RV=%sysfunc(stpsrv_header(Content-type, application/vnd.ms-excel));*/
/*%let RV=%sysfunc(stpsrv_header(Content-disposition, attachment;filename="Completion_Weekly.xls")); */
%STPBEGIN;
/*ods tagsets.ExcelXP file=_webout style=sasweb options(doc="help");*/
proc print data=sashelp.class;
run;
quit;
ods tagsets.ExcelXP close;
%STPEND;

This seems to be working fine. I am getting a very long xml code inside the webpage. But when I include this statement -
/*ods tagsets.ExcelXP file=_webout style=sasweb options(doc="help");*/

I am getting this error in the xml-log-html page.

The XML page cannot be displayed

Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.

--------------------------------------------------------------------------------

Only one top level element is allowed in an XML document. Error processing resource 'http://sasdev.uhc.com:8080/SASStoredPr...

-^
AN> ss:ID="systemtitle5__c" ss:Parent="systemtitle5">

When I include the headers and allow the XML file to be generated, I am getting a "Strict Parse Error".

Does this sound familiar?

Thanks,
saspert. Message was edited by: saspert

Re: Running ExcelXp tagsets in Stored Processes

Vince_SAS — Tue, 01 Mar 2011 17:44:22 GMT

The MPRINT should show you that STPBEGIN is issuing an ODS statement for you, so your extra ODS statement is causing the problem.

Try this:
[pre]
*ProcessBody;
options mprint;
ods path work.tmplmst(update) sashelp.tmplmst(read);
%include '/sasdev/optum/rpt/phi/projects/eHP/Release2/Development_Code/excel_120/excltags.sas';
%let _ODSDEST=tagsets.ExcelXP;
%let RV=%sysfunc(stpsrv_header(Content-type, application/vnd.ms-excel));
%let RV=%sysfunc(stpsrv_header(Content-disposition, attachment;filename="Boeing_HA_Completion_Weekly.xls"));
%STPBEGIN;
proc print data=sashelp.class; run; quit;
%STPEND;
[/pre]

Vince DelGobbo
SAS R&D

Re: Running ExcelXp tagsets in Stored Processes

saspert — Tue, 01 Mar 2011 19:13:27 GMT

Actually Vince, I want to know where I should put the additional ODS statements like
ODS options (tab1 ...options for tab1);
ODS options (tab2 ...options for tab2);
ODS options (tab3 ...options for tab3);

Do you know if putting these statements between the STPBEGIN and STPEND statements will cause issues?

Thanks,
Raj.

Re: Running ExcelXp tagsets in Stored Processes

Vince_SAS — Tue, 01 Mar 2011 19:43:28 GMT

Recall that you can have multiple ODS statements, and you can specify the options on the extra statements:
[pre]
*ProcessBody;

options mprint;

ods path work.tmplmst(update) sashelp.tmplmst(read);

%include '/sasdev/optum/rpt/phi/projects/eHP/Release2/Development_Code/excel_120/excltags.sas';

%let _ODSDEST=tagsets.ExcelXP;

%let RV=%sysfunc(stpsrv_header(Content-type, application/vnd.ms-excel));
%let RV=%sysfunc(stpsrv_header(Content-disposition, attachment;filename="Boeing_HA_Completion_Weekly.xls"));

%STPBEGIN;

ods tagsets.ExcelXP options(sheet_name='Male Students');

proc print data=sashelp.class;
where (sex eq 'M');
run; quit;

ods tagsets.ExcelXP options(sheet_name='Female Students');

proc print data=sashelp.class;
where (sex eq 'F');
run; quit;

%STPEND;
[/pre]

Vince DelGobbo
SAS R&D

Re: Running ExcelXp tagsets in Stored Processes

saspert — Tue, 01 Mar 2011 20:58:35 GMT

Ah - so the main ODS statement conflicts but not the other ODS statements. Thanks for the handholding.

This code does work fine - I get back the 2-tab spreadsheet. But when I try to insert my original code, I get back errors. I looked in the MSO folder like your suggestion in one of the earlier posts. I am getting this error -

XML PARSE ERROR: Null or missing attribute value
Error occurs at or below this element stack:

When I comment the headers, I get this in the XML log window

-

Re: Running ExcelXp tagsets in Stored Processes

Vince_SAS — Wed, 02 Mar 2011 23:37:36 GMT

I suggest you open a track with Technical Support:

http://support.sas.com/ctx/supportform/createForm

You are getting malformed XML, so one last thing to check is to make sure that you don't have any ODS statements that conflict with the one generated by STPBEGIN.

Good luck.

Vince DelGobbo
SAS R&D Corrected typo.

Message was edited by: Vince@SAS

Re: Running ExcelXp tagsets in Stored Processes

saspert — Thu, 03 Mar 2011 22:41:17 GMT

Sure. I think I will review the code with my team before submitting a track.
Thank you for your tips.

Running ExcelXp tagsets in Stored Processes

patel1987 — Sat, 24 Dec 2011 23:32:46 GMT

Hi,

I am lokking for this solution. Because I am really wondering to do this job done. Right now I m working on this code whichever you mentioned. So I am really appreacite that you give me this solution to Create title page in excel via XML rpeort.

Thanks.