Your SAS programs, embedded in web apps and elsewhere

Stored Process and Excelxp Templates

Reply
N/A
Posts: 0

Stored Process and Excelxp Templates

Hi there
I am in a process of converting some ODS files to SPs. The challange I am facing is how do I convert excelXP template options to SP , so that they will behave same when run through the Excel add in. I tried many different options and didn't go any where. Can anyone help.
fo e.g , here is the ODS code
data a;
input subvar $ xvar mmddyy6. vbar_y plot_y;
datalines;
sub1 010196 10 8
sub1 020196 20 15
sub1 030196 30 55
sub1 040196 40 50
;
ods listing close;
ods tagsets.Excelxp file="/home/myhome//test2.xls";
ods tagsets.Excelxp options(sheet_name="mydata" frozen_header='2' autofilter='all');
proc print data=a;
run;
ods tagsets.Excelxp close;
ods listing;

How do I get the same output , if run through an SP in excel addin? I would like to have the frozen header and the filer when the user run it in excel. Is it possible to do this using SP. If not is there any other way?

Thanks in advance
Suren
SAS Super FREQ
Posts: 8,743

Re: Stored Process and Excelxp Templates

Hi:
At this time, I believe that the SAS Add-in for Microsoft Office (Excel) does NOT support a stored process that returns output from the ExcelXP destination. When I tested this a while back, a stored process that used TAGSETS.EXCELXP could only be executed from the Information Delivery Portal, the Stored Process Web Application, a custom application -- or MAYBE SAS Enterprise Guide. Generally, speaking, if you were going to use those client applications (or write your own app), you could code the stored process as one of the following:
[pre]
ods tagsets.excelxp file=_webout style=sasweb
options(sheet_name="mydata" frozen_header="2" autofilter="all");
...code ...
ods tagsets.excelxp close;
[/pre]

an alternative method would be:

[pre]
%let _odsdest = tagsets.excelxp;
%let _odsstyle = sasweb;
%let _odsoptions=%str(options(sheet_name="mydata" frozen_header="2" autofilter="all"));
%stpbegin;
...code ...
%stpend;
[/pre]
However, 1) you might need to use different invocations for different client applications and 2) you might need to send your own content-type header for web results, to tell the receiving machine to launch Excel for the output stream instead of the browser.

But, as I said, I do NOT believe that the Add-in for Excel will accept/receive tagsets.ExcelXP XML results at this time. For the final word on this subject, you can check with Tech Support. I don't know enough about Excel to know how you would implement frozen headers or autofilters from the Excel end instead of the SAS end.

cynthia
N/A
Posts: 0

Re: Stored Process and Excelxp Templates

Thanks for the quick respond Cynthia. I think Excelxp and the information Portel should do the trick for me.

cheers
Suren
SAS Super FREQ
Posts: 8,743

Re: Stored Process and Excelxp Templates

That's good that you can use the Information Delivery Portal.

Since your original posting referred to the "Excel add-in", I wasn't sure that the Portal would be an option. There is a difference between running the stored process using the SAS --> Reports menu choice in Excel (in which ExcelXP is NOT supported) and running the stored process via the Portal or the Stored Process Web Application (in which the use of TAGSETS.EXCELXP is supported).

Do check out the STPSRV_HEADER function, which is how you will have to change the content-type header. Also, if you can use the Information Delivery Portal to launch the stored process, that means you have the Web Infrastructure Kit installed & so, you might also check out the Stored Process Web Application, which would allow you to launch the stored process from a URL, which is totally cool.

cynthia
New Contributor
Posts: 3

Re: Stored Process and Excelxp Templates

I am trying to open up Excel from a stored process within IDP. However, I get an XML file in a browser instead. Any thoughts?

I am using the following code:
%let _odsdest=tagset.ExcelXP;
data _null_ ;
rc = stpsrv_header('Content-type', 'application/vnd.ms-excel') ;
rc = stpsrv_header('Content-disposition', 'attachment; filename=temp.xlsx') ;
run ;

I would have thought that the first call would be pointing it to open Excel.

Thanks for any suggestions.
SAS Employee
Posts: 285

Re: Stored Process and Excelxp Templates

The calls to STPSRV_HEADER must be positioned such that they are the first data written to _WEBOUT. That means that they must precede any ODS statements (or STPBEGIN if you are using that).

Also, you should use "xml" for the file extension, to prevent contentions with Excel 2007.

Vince DelGobbo
SAS R&D
New Contributor
Posts: 3

Re: Stored Process and Excelxp Templates

Thanks, Vince.

I tried moving it ahead of the _ODSDEST override (it was already before the %STPBEGIN), but that didn't do the trick either. Is there something else that might be going on in the portal that is writing to _webout prior to the stored process call?
SAS Super FREQ
Posts: 8,743

Re: Stored Process and Excelxp Templates

Hi:
It sounds like your stpsrv_header function is not being used. You might want to work with Tech Support on this issue. When I submit either version of this stored process using the Information Delivery Portal, Excel launches to open my results.

Do note the placement and usage of %stpbegin in Example 1 versus the fact that there's no %stpbegin in Example 2. If neither of these stored processes work to launch Excel when invoked from the Portal, then you will want to work with Tech Support on this question.

cynthia

Stored Process Example 1:
[pre]
*ProcessBody;
data _null_;
rc = stpsrv_header('Content-type',"application/vnd.ms-excel");
rc = stpsrv_header('Content-disposition',"attachment; filename=ex_xp1.xls");
run;

%let _odsdest = tagsets.excelxp;
%let _odsstyle = sasweb;

%stpbegin;

proc print data=sashelp.class noobs;
title 'test 1 to Excel using stpbegin';
run;

%stpend;
[/pre]

Stored Process Example 2:
[pre]
data _null_;
rc = stpsrv_header('Content-type','application/vnd.ms-excel');
rc = stpsrv_header('Content-disposition','attachment; filename=ex_xp2.xls');
run;
ods listing close;


ods tagsets.excelxp body=_webout style=sasweb;

proc print data=sashelp.class noobs;
title 'test 2 using _webout to Excel';
run;


ods _all_ close;
[/pre]

UPDATE: Vince is correct in the file extension that you specify. Excel 2007 has a new message that Excel 2003 did not have. Excel 2003 did not complain about giving an XML file the extension of ".XLS". But the XML created by TAGSETS.EXCELXP is NOT the same as a 2007 ".XLSX" file, so that extension is incorrect. A file extension of ".XML" is the most accurate for TAGSETS.EXCELXP output because the file markup is actually Office 2003 Spreadsheet Markup Language XML. The code that I posted was opened with Excel 2003 on the client machine.
New Contributor
Posts: 3

Re: Stored Process and Excelxp Templates

I was able to get your example to work, but...

I have a macro that includes the *processbody; %stpbegin, etc. statements along with other items. When I use this macro to do the same thing as example 1, it fails to open Excel. I get an "Illegal tag" error. I am going to pass this along to Tech Support.

Thank you for your help. It certainly gotten me further down the path.
SAS Employee
Posts: 285

Re: Stored Process and Excelxp Templates

If you are getting the illegal tag error when you attempt to open the file with Excel, then it indicates malformed XML. Be sure you have a recent version of the tagset on server (the version number is written to the SAS log when you open the destination).

Vince DelGobbo
SAS R&D
SAS Employee
Posts: 1

Re: Stored Process and Excelxp Templates

Hi,

I have similar issue. The following code works fine in
1. Base SAS, E Guide ( SAS 9.1.3 SP4) but if I create a stored process(in E guide) and try to open through AMO ( MS Office 2007) gives error. Some times does create the excel file but with No frozen headers or filters.
2. Base SAS, E Guide ( SAS 9.2 TS2M2) but if I create a stored process(in E guide) and try to open through AMO ( MS Office 2007), it creates excel file but with No frozen headers or filters.
data a;
input subvar $ xvar mmddyy6. vbar_y plot_y;
datalines;
sub1 010196 10 8
sub1 020196 20 15
sub1 030196 30 55
sub1 040196 40 50
;
ods listing close;
ods tagsets.ExcelXP file="E:\Test\test6.xls" style=sasweb;
ods tagsets.ExcelXP options(sheet_name="mydata" frozen_headers='2' autofilter='all');

proc print data=a;
run;
ods tagsets.ExcelXP close;
ods listing;

Do you know if it is possible to convert existing ODS code containing especially(ExcelXP or msoffice2K tagsets) to a stored process so that it throws same results as in BaseSAS or Eguide?



Sunil
SAS Employee
Posts: 285

Re: Stored Process and Excelxp Templates

Take a look at Cynthia's earlier message: the SAS Add-In for Microsoft Office does not support output from the ExcelXP tagset.

You might want to also explore the Integration with Microsoft Office forum:
http://support.sas.com/forums/forum.jspa?forumID=7

Vince DelGobbo
SAS R&D
Ask a Question
Discussion stats
  • 11 replies
  • 675 views
  • 0 likes
  • 5 in conversation