The SAS Output Delivery System and reporting techniques

Naming a cell or range of cells in Excel output using tagsets.excelxp

Reply
Contributor
Posts: 22

Naming a cell or range of cells in Excel output using tagsets.excelxp

Is it possible to name a cell or range of cells in an ODS excel output using the ExcelXp tagset? If so is it possible to do using a call define style override statement in a compute block?
SAS Employee
Posts: 95

Re: Naming a cell or range of cells in Excel output using tagsets.excelxp

It is not currently possible to name a cell or range of cells with the ExcelXP tagset.

It should be possible. doing it in a way that is usable from a compute block is the challenge.
SAS Super FREQ
Posts: 8,864

Re: Naming a cell or range of cells in Excel output using tagsets.excelxp

Hi, Eric:
Hmmmm, you make it sound like PROC REPORT and COMPUTE blocks are deficient. I suspect it's really the fact that Microsoft Spreadsheet XML is so convoluted that's causing the difficulty.

PROC REPORT is totally wonderful and if the COMPUTE block could read Excel's mind, I'm sure it -would- do everything possible to name that range of cells. ;-)

cynthia
N/A
Posts: 0

Re: Naming a cell or range of cells in Excel output using tagsets.excelxp

Posted in reply to Cynthia_sas
I had hoped to discover a formula/function in excel that could define/create a range name. Nothing has come up so far.
With "old tech" (dde) I used to execute vba macros, but there does not seem to be a formula/function to invoke vba either.
We take a lot for granted in our environments with the level of integration provided by the SAS System as standard. So it is a bit disappointing to find the level of integration less flexible in those "external" (non-SAS) clients on which we and our clients/customers depend.
(score another point for SAS)

PeterC
Frequent Contributor
Posts: 102

Re: Naming a cell or range of cells in Excel output using tagsets.excelxp

Posted in reply to deleted_user
I remember looking at this a while back, and deciding that:

- It would probably be possible to create names for simple rectangular ranges, but

- It would be difficult to create the complicated named ranges that sophisticated Excel users come up with. Interestingly, I could create named ranges using the Excel GUI that I could not create using VBA (in particular, discontiguous ranges containing many areas).

If a new feature is to be added, I'd prefer pivot tables.
SAS Super FREQ
Posts: 8,864

Re: Naming a cell or range of cells in Excel output using tagsets.excelxp

Posted in reply to JackHamilton
Jack:
I think that TAGSETS.TABLEEDITOR will do pivot tables, the only hitch is that first you create an HTML file and once the HTML file starts loading, then the contents are saved to Excel. But here it says (on 2nd link) that pivot tables are possible.
http://support.sas.com/rnd/base/ods/odsmarkup/tableeditor/index.html
http://support.sas.com/rnd/base/ods/odsmarkup/tableeditor/index.html#pivot

cynthia
Occasional Contributor
Posts: 14

Re: Naming a cell or range of cells in Excel output using tagsets.excelxp

The excel engine will do that for you... not formulas though.
Valued Guide
Posts: 2,177

Re: Naming a cell or range of cells in Excel output using tagsets.excelxp

how can you "use" the "excel engine" through tagsets.excelxp?
Occasional Contributor
Posts: 14

Re: Naming a cell or range of cells in Excel output using tagsets.excelxp

Well, you can't use the "excel engine" through tagsets.excelxp but I think what you could do is to create the range name using the libname excel "excelfile.xls"

and then modify it through tagsets.excelxp with whatever your report/table is- I haven't tried it though, it just a suggestion. Message was edited by: _Altons_
Valued Guide
Posts: 2,177

Re: Naming a cell or range of cells in Excel output using tagsets.excelxp

with SAS/Access to PC Files, the SAS library engine that interfaces excel, will create range names in excel for example with[pre] libname pcfmts 'demo.xls' ;
proc copy in= sashelp out= pcfmts ;
select class ;
run ;[/pre]should create range name CLASS .
Similarly range names in an excel workbook might be able to be read as "data sets".
The SAS/Access to ODBC product allows similar access to excel ranges as data sets.
While these SAS/Access products allow read, create and write access of excel range data, they don't change formatting (handled extensively by tagsets.ExcelXP).
On the other hand Tagsets.excelXP will only create new workbooks, so it cannot update an existing range. There appears to be one more restriction with Tagsets.excelXP. As these are not the excel "binary" format, you cannot "link" into them. [perhaps that is handled better in Excel 2007]

PeterC
SAS Super FREQ
Posts: 8,864

Re: Naming a cell or range of cells in Excel output using tagsets.excelxp

Peter:
If you use the CONTENTS=yes suboption with ExcelXP, you can create a TOC with links that WILL click down into the other worksheets. And, in the program below, I have created links in the footnote to navigate from page to page. Then on sheet One, if you click on any name, it will take you to sheet 4.

With PROC REPORT, you could further customize the link, but I already had this code that just showed that the link worked. The key, when using LINK= or URL=, with ExcelXP is to use a relative name that starts with the Workbook name and to let the tagset template build everything else.

cynthia

[pre]
ods tagsets.excelxp file='all_three.xls' style=sasweb
options(contents='yes' sheet_name='One'
embedded_footnotes='yes');
ods proclabel 'Top Node 1';
proc print data=sashelp.class(obs=3) contents='One: Class';
var name / style(data)={url="all_three.xls#'Four'!A1"};
footnote link="all_three.xls#'Two'!A1" 'Go to Two';
run;

ods proclabel 'Top Node 2';
ods tagsets.excelxp options(sheet_name='Two');
proc print data=sashelp.shoes(obs=3) contents='Two: Shoes';
footnote link="all_three.xls#'Three'!A1" 'Go to Three';
run;

ods proclabel 'Top Node 3';
ods tagsets.excelxp options(sheet_name='Three');
proc print data=sashelp.prdsale(obs=3) contents='Three: PrdSale';
footnote link="all_three.xls#'One'!A1" 'Go to One';
run;

ods proclabel 'Top Node 4';
ods tagsets.excelxp options(sheet_name='Four');
proc print data=sashelp.class contents='Four: More Class';
footnote link="all_three.xls#'Contents'!A1" 'Go to Contents';
run;
ods tagsets.excelxp close;
[/pre]
Valued Guide
Posts: 2,177

Re: Naming a cell or range of cells in Excel output using tagsets.excelxp

Posted in reply to Cynthia_sas
Thank you Cynthia

I have tried the internal links offered with TOC and Index. (URL=none proved helpful).
I failed to make external links from a binary excel workbook into a cell of a workbook created with tagsets.excelxp. Once the xml-workbook was saved as a binary-excel-workbook, these external links worked.

A "nice-to-have" feature: use a static "reconcilliation sheet" in excel which refers into the workbooks created with tagsets.excelXP.

PeterC
Frequent Contributor
Posts: 102

Re: Naming a cell or range of cells in Excel output using tagsets.excelxp

Almost all of my creation of workbooks is through batch jobs run on z/OS, slowly switching to AIS - so platform-specific solutions such as the excel libname are not useful to me.

The tableeditor tagset looks interesting. The examples don't show the direct creation of Excel XML, without opening a (platform specific) browser, but perhaps that is possible.

The proliferation of tagsets is interesting - let a hundred flowers blossom and a hundred schools of thought contend - but in some ways unfortunate, because there's no single solution. It would be good if the added features from tableeditor could be fioded into excelxp, and perhaps vice versa (I don't kow what tableeditor is missing).

The people who create and support the tagsets have done a wonderful job, and new features are constantly being added, so perhaps this is in the future.
SAS Super FREQ
Posts: 8,864

Re: Naming a cell or range of cells in Excel output using tagsets.excelxp

Posted in reply to JackHamilton
Jack:
I -think- that tagsets.tableeditor uses HTML and then some kind of Javascript or VB script to write to Excel once the HTML page loads. It's taking advantage of some of the onload, onclick scripting stuff, I think. And it only works with IE.

Tagsets.ExcelXP is pure XML -- Microsoft Spreadsheet ML XML. I don't know how Micosoft interacts with embedded scripts or even whether they would work in the 2003 flavor of Spreadsheet ML XML.

Microsoft technology is dark and murky to me. So I don't know whether it is even possible to meld the two technologies (HTML/XML) into one tagset template from the SAS end. Also, Microsoft has further muddied the waters with the new ".xlsx" format. What I find totally amazing is that you can rename an ".xlsx" file to ".zip" and then open the file with WinZip and see that .xlsx files are really just a collection of related files -- some xml, some css, etc. So that's 3 differing technologies to keep up with HTML-based, 2003-XML-based and 2007-"new"-XML-based.

I really admire the ODS folks and Tech Support and Tagset Template people for managing to know so much SAS -and- so much Microsoft technology.

cynthia
Ask a Question
Discussion stats
  • 13 replies
  • 878 views
  • 0 likes
  • 7 in conversation