The SAS Output Delivery System and reporting techniques

Data _null_ excel links

Reply
Contributor
Posts: 21

Data _null_ excel links

Is it possible to create excel workbook that has internal links? This all should be done with tagsets.excelxp and data _null_ as the layout of the document is quite spesific.
Of course it can all be done with proc print or proc report but if data _null_ statement an links was to be used?
SAS Super FREQ
Posts: 8,868

Re: Data _null_ excel links

Hi:
I'm not sure what you mean by "links" or what you mean by using "data _null_ as the layout of the document is quite specific".

About LINKS:
This forum posting shows how to insert hyperlinks to other web sites:
http://support.sas.com/forums/thread.jspa?threadID=1212

This forum posting discusses how to code "internal" links within the Excel worksheet, as in a Table of Contents, using the CONTENTS= suboption:
http://support.sas.com/forums/thread.jspa?messageID=26133昕

And this previous forum posting shows how to put a link in a footnote statment:
http://support.sas.com/forums/thread.jspa?messageID=17732䕄

I'm not sure what other kinds of "links" you mean. You might mean pointing to other columns, as for formulas, but formulas are implemented via the use of the TAGATTR style attribute. If you search the forum, there are many postings about the use of TAGATTR.

About DATA _NULL_:
You said you wanted/needed to use DATA _NULL_ because the layout of the document is quite specific. I believe you may find that "free-format" DATA _NULL_ does not work with ODS in the same way it worked in the LISTING window. So, for example, if you wanted to do something like this:

1) put the first NAME in cell B1 and then put that person's address in C2 and their city/state in C3 and then put their employer in F3
2) put the second NAME in cell B5, address in C6, city/state in C7 and employer in F7
3) put the third NAME in cell B9, address in C10, city/state in C11 and employer in F11
but then after 10 names, switch to a different layout, where you put every name in a different cell with that person's address in the cell directly underneath the name.

The above report would be hard to accomplish. Individual "cells" are not addressable when you use DATA _NULL_ and TAGSETS.EXCELXP. The only way I know to do something as described above is through the use of DATA _NULL_ and DDE -- which is bypassing ODS altogether and is writing directly into a binary Excel worksheet. (Note that I described an arbitrary layout -- your layout is probably more "organized" -- but the important point is that SAS and ODS expect output to be in the form of TABLES -- a very regular arrangement of rows and columns, where every row has the same number of columns and the only row or column spanning that happens will happen in the Headers or RowHeaders of the report table.)

PROC PRINT and PROC REPORT and PROC TABULATE are better tools for creating output for report tables. You can also use DATA _NULL_ for report tables, but, you can only create TABLES when you use DATA _NULL_ and ODS, as currently available. You can't create "free-format" DATA _NULL_ output. The new ODS LAYOUT will allow you more free-format report capabilities for ODS PDF and ODS RTF, and possibly ODS HTML, but I don't know whether ODS LAYOUT type of region control will work with TAGSETS.EXCELXP. And, since ODS LAYOUT is experimental (or pre-production) at this point, the syntax could still change between how it works now and how it will work when the capability becomes production.

If your document/workbook layout is quite specific about print controls, orientation, print headers and footers, etc, then this paper will be of some help in explaining the print controls available to you with TAGSETS.EXCELXP.
http://www.nesug.org/proceedings/nesug08/ap/ap06.pdf

For more help in using TAGSETS.EXCELXP to create your output, you might wish to open a track with Tech Support, as they could look at an example of the output you need to create and look at your data and help you figure out the best approach.

cynthia
Contributor
Posts: 21

Re: Data _null_ excel links

Posted in reply to Cynthia_sas
Hi

Obviously I suffer from baby blues or whatever.. so much has been forgotten.. But at the moment I have following elements on my excel printing:

A Proc Template creating two table templates and conclusion page uses following definitions:

define selite;
generic=on;
style=data{url=$urli.};
end;

...
ods tagsets.excelxp file='c:\temp\tilaus.xls'
options( embedded_titles='yes' embedded_footnotes='yes' );
%macro printtaa;
;
%do times = 1 %to &kpl;
%let place=%scan(&names,×,*);
ods tagsets.excelxp options(sheet_name="&place");
...
and the data _null_ step where all that is calculated before will be put into ods destination. In the data _null_ step there are several set-statements, several merge-statements and other things.

Before defining the table template, a format has to be create for refferring the right sheets on the workbook. Only cntlin can be used, no macro variable references and generating the proc format code.
..
%do times = 1 %to &kpl;
%let part=%str(%')%scan(&names,×,*)%str(%');
start="%scan(&names,×,*)";
label="#'"||trim(left(compbl(start)))||"'!A1";
output;
%end;
SAS Super FREQ
Posts: 8,868

Re: Data _null_ excel links

Hi:
Ah, I see. If you're using a TABLE template, then you are creating tabular output with DATA _NULL_ and not "free-format" output. Within the limitations of what you can do with the ExcelXP destination, you should be able to get tabular output from DATA _NULL_ into Excel using TAGSETS.EXCELXP.

It looks to me like your macro is building a format for a link, of the form:
[pre]
wbname.wbext#'Sheet'!Cell" 'LinkText'
[/pre]
--wbname.wbext = the name and ext of the workbook
--Sheet = the sheet name
--Cell = the cell location for the LINK (I usually just code A1 here) and then
--LinkText = the text that the link will display (you may only need this in a title or footnote statement)
[/pre]

For example, in one of the previous forum postings that I referred to, the code is using the Footnote statement to contain a LINK:
footnote link="all_three.xls#'One'!A1" 'Go to One';

which is pointing to all_three.xls workbook, the Sheet named One, the Cell A1. when the user clicks on the text "Go to One", they would get taken to that workbook, that sheet and their cursor will be in that cell.

Your Table template is -NOT- using a Footnote statement, however, your table template is using a URL= style attribute to set the URL for the cell. I'm not sure what your question is. Assuming you have defined the building of the link correctly in your program, your links should work.

If you are having trouble using URL= in your TABLE template with TAGSETS.EXCELXP, your best source of help would be to open a track with Tech Support.

cynthia
Ask a Question
Discussion stats
  • 3 replies
  • 146 views
  • 0 likes
  • 2 in conversation