BookmarkSubscribeRSS Feed
RobW
Calcite | Level 5
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?
15 REPLIES 15
Eric_SAS
SAS Employee
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.
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
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
JackHamilton
Lapis Lazuli | Level 10
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.
Cynthia_sas
SAS Super FREQ
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
_Altons_
Calcite | Level 5
The excel engine will do that for you... not formulas though.
Peter_C
Rhodochrosite | Level 12
how can you "use" the "excel engine" through tagsets.excelxp?
_Altons_
Calcite | Level 5
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_
Peter_C
Rhodochrosite | Level 12
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
Cynthia_sas
SAS Super FREQ
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]
Peter_C
Rhodochrosite | Level 12
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
ToTylkoGra
Fluorite | Level 6
Tank You Sythia ! You are realy inquisitive and wise ! It is very helpfull. I would have another question. How to change the name of the sheet 'contents' and 'index' to different name like 'this is contents' or 'list of tables' . Same I want to acomplish to the index table.It is automaticly created when used with options in tagsets.excelxp. Please help !
Cynthia_sas
SAS Super FREQ

Hi:

  It's not a good idea to post your new question in a post from 2009. And your question is not really related to the original topic of this post. If you have a new question, it's probably a better idea to make a new posting. It may be easier to create the Contents or Index and change the label after you open the file with Excel. The change you want may have to be done with a TAGSET template change, I do not believe you can make this change with a style. This type of question would best be handled with Tech Support. I am not sure whether they recommend trying to modify tagset templates anymore. The TAGSETS.EXCELXP tagset is complex and in changing one thing, you could break another. I would recommend working with Tech Support on this question.

Cynthia

JackHamilton
Lapis Lazuli | Level 10
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-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
  • 15 replies
  • 3200 views
  • 2 likes
  • 8 in conversation