BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
11 REPLIES 11
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
Thanks for the quick respond Cynthia. I think Excelxp and the information Portel should do the trick for me.

cheers
Suren
Cynthia_sas
SAS Super FREQ
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
kcranford
Calcite | Level 5
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.
Vince_SAS
Rhodochrosite | Level 12
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
kcranford
Calcite | Level 5
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?
Cynthia_sas
SAS Super FREQ
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.
kcranford
Calcite | Level 5
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.
Vince_SAS
Rhodochrosite | Level 12
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
Bhadauriya
SAS Employee
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
Vince_SAS
Rhodochrosite | Level 12
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1744 views
  • 0 likes
  • 5 in conversation