The SAS Output Delivery System and reporting techniques

Putting in anchors

Reply
N/A
Posts: 0

Putting in anchors

For some weird reason, I just can not get this to work.
I have a dataset (paylines) I'm trying to write (conditionally) anchors. The names are in the variable anchor, and I thought all I'd have to do is check when the first column wasn't blank to write it out, but it just ain't happ'n. What I have below is the latest incarnation - I've tried various incarnations: PRETEXT, Cat, etc.

Just to further complicate matters, I'm trying to write this to an ('the evil') Excel spreadsheet. I have another worksheet that I want to eventually use an href to point to the records. IE, I have a cell in the first sheet that, when clicked, will go to the first record (position, emplid) of the paylines sheet.

ODS TAGSETS.EXCELXP OPTIONS(SHEET_NAME="PayRecords");

PROC REPORT DATA=paylines(OBS=1000) NOWD MISSING;
COLUMN anchor position_nbr emplid enddte project_id;

DEFINE anchor/GROUP NOPRINT;
DEFINE position_nbr/GROUP "Position Number";
DEFINE emplid/GROUP;
DEFINE enddte/GROUP FORMAT=MONYY5. ORDER=INTERNAL "Month";
DEFINE project_id/ ORDER FORMAT=$15. "Project ID";

COMPUTE BEFORE position_nbr;
IF Trimn(position_nbr) NE '' THEN Call Define(_row_, "STYLE", Cat("STYLE=[","PREHTML=",'""',"]"));
ENDCOMP;

COMPUTE project_id;
Call Define(_row_, "STYLE", 'STYLE=[POSTHTML="
"]');
ENDCOMP;
RUN;

ODS TAGSETS.EXCELXP CLOSE;
SAS Super FREQ
Posts: 8,864

Re: Putting in anchors

Posted in reply to deleted_user
Hi!
You can use the URL style attribute directly. Or, the CALL DEFINE statement also has the URL, URLP and URLBP attributes available to you.
Try this code...it works for me in both ExcelXP and MSOffice2K -- using just the simple URL= attribute. The difference is that you can see the links underlined in the MSOFFICE2K output, but using my version of TAGSETS.EXCELXP, the links are NOT underlined, but if I hover my mouse over a cell, the cursor changes from an arrow to a hand and the links still work.

I realize this isn't exactly the same scenario as the one you outline, but I'm not entirely clear on how to do "intersheet" linking in plain old Excel. However, if you think that an HTTP tag will work for you, then I think you should start with the URL style attribute and go from there...






























N/A
Posts: 0

Re: Putting in anchors

Posted in reply to Cynthia_sas
Cynthia:
Seems like you were on the right track.

I went back in and added a row variable (and took out the anchor).

Then I left joined the source data set to the paylines and brought in the row

In the proc report for the source, I put in the following compute and write it to its own worksheet:

COMPUTE ts_cert;
IF -2 <= ts_cert <= -1 THEN CALL DEFINE(_col_,"URL",Catt('#payrecords!A',Put(row+1,5.-l)));
ENDCOMP;

The row + 1 is needed because the header creates a second heading line. On first blush, it creates a conditional hyperlink and if available and clicked, so far goes to the correct line.
In the url, the # indicates the worksheet, the ! indicates the column.

One last note. I went in and went over the tagset code. Apparently, the tagattr does some funky stuff, like puts the attribute (any attribute) in ss:format and it looks like pre/post html and text aren't handled.

Thanks for the lead,
Richard Wright
SAS Super FREQ
Posts: 8,864

Re: Putting in anchors

Posted in reply to deleted_user
Richard:
Glad you got it working! I'm not surprised that TAGATTR worked differently for ExcelXP than it does with HTML. That's the fundamental difference between writing HTML and writing Spreadsheet Markup Language -- which is Microsoft XML. You can specify either a format or a formula with TAGATTR and ExcelXP -- according to the XML spec I think those DO go into SS: tags....so ExcelXP is doing what its supposed to do.

I did not know that you could make a URL/internal link using the sheet name and and cell address. That is very cool!

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