BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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;
3 REPLIES 3
Cynthia_sas
SAS Super FREQ
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...






























deleted_user
Not applicable
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
Cynthia_sas
SAS Super FREQ
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

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
  • 3 replies
  • 821 views
  • 0 likes
  • 2 in conversation