topic Re: assigning hyperlink to a variable in ODS and Base Reporting

assigning hyperlink to a variable

Elliott — Fri, 07 Sep 2018 18:21:52 GMT

I posted the other day about creating hyperlinks with ods and proc report. I was able to create the hyperlink and it is in the right most column of my report. The user does not want that extra column and asked if I could have the hyperlink embedded in the report_number variable. Can that be done? can I assign the variable value to the link and hide the column with the actual full link information?

Thanks,

Elliott

Re: assigning hyperlink to a variable

Reeza — Fri, 07 Sep 2018 19:17:56 GMT

Yes, the formula part takes one part as text and one as the link.

Set them appropriately and you should be fine.

@Elliott wrote:

I posted the other day about creating hyperlinks with ods and proc report. I was able to create the hyperlink and it is in the right most column of my report. The user does not want that extra column and asked if I could have the hyperlink embedded in the report_number variable. Can that be done? can I assign the variable value to the link and hide the column with the actual full link information?

Thanks,

Elliott

Re: assigning hyperlink to a variable

Elliott — Fri, 07 Sep 2018 19:31:31 GMT

I don't know how.

each row will have a different report number that I need to assign to the link.

this is the code I have now that creates the report:

proc report nofs data=work.zero_vol1 list;

title "No Error Volume";

column DT REPORT_NUMBER REPORT_ID ERRS VOL COMMENTS LINK;

define link /display;

compute link ;

href=trim(link)||".html";

call define(_col_, "URL", href);

endcomp;

run;

Re: assigning hyperlink to a variable

Reeza — Fri, 07 Sep 2018 19:42:21 GMT

In your previous question, a suggestion was made by @SuryaKiran to use a formula instead. That is the approach you can use if the URL is different than the text.

https://communities.sas.com/t5/ODS-and-Base-Reporting/creating-hyperlinks/m-p/491711/highlight/true#M21588

Re: assigning hyperlink to a variable

Cynthia_sas — Sun, 09 Sep 2018 15:57:35 GMT

Hi:

This approach worked for me. I just used NOPRINT to hide the computed link and then used the CALL DEFINE on NAME instead.

ods html path='c:\temp' (url=none) file='hidelink.html';
ods excel file='c:\temp\hidelink.xlsx';
proc report data=sashelp.class;
  column name age sex height href;
  define name / display;
  define href / computed noprint;
  compute href / character length=100;
    href = catt('https://nameberry.com/babyname/',name);
	call define('name','url',href);
  endcomp;
run;
ods _all_ close;

Cynthia

Re: assigning hyperlink to a variable

Elliott — Mon, 10 Sep 2018 18:28:27 GMT

Cynthia, this has been helpful and I am getting the link on the correct variable but the link goes to C:\...\Local\Microsoft\Windows\Temporary\Internet Files\Content,Outlook\BSC00BHK\

It did not pick up the string from the LINK variable. The value in the LINK variable is the full address to the SharePoint folder starting with https://

this is my code:

proc report nofs data=work.zero_vol1 list;

title "Reports with No Error Volume";

column DT REPORT_NO REPORT_ID ERRS VOL COMMENTS LINK;

define DT / display;

define REPORT_NO / display;

define REPORT_ID / display;

define ERRS / style(column)=[tagattr='format:0'];

define VOL / style(column)=[tagattr='format:#,##'];

define link /computed noprint;

compute link ;

href=catt(link,'.html',REPORT_NUMBER);

call define('REPORT_NUMBER','url', href);

endcomp;

run;

I also get a note in the log that says:

NOTE: The computed variable LINK is also a data set variable.

NOTE: The output might not be as expected.

I am not sure if that is important on not.

Thank you very much for your help

Elliott

Re: assigning hyperlink to a variable

Reeza — Mon, 10 Sep 2018 18:35:02 GMT

Can you show a screenshot of what you're seeing in Excel?

Re: assigning hyperlink to a variable

Elliott — Mon, 10 Sep 2018 18:39:23 GMT

when I hover over the link this is what it shows

Re: assigning hyperlink to a variable

Reeza — Mon, 10 Sep 2018 18:44:17 GMT

Does @Cynthia_sas code work for you as expected?
Did you try changing the variable names (link) to avoid the NOTES in the log?
What does the URL look like in the variable link, and how does it look after concatenated?

I tested this with an HTTPS link to my Sharepoint site and it worked fine - using SAS 9.4 TS1M5.

@Elliott wrote:

when I hover over the link this is what it shows

Re: assigning hyperlink to a variable

Elliott — Mon, 10 Sep 2018 18:50:56 GMT

Cynthia's did make the link on the correct variable, and hid the actual variable that had the full http path. but the link that got created on the report no variable was to that location in the screen shot, not the sharepoint link.

I did try to change the variable name but the result was not correct..

the url looks like https://...just like any link to a sharepoint site.

I had this working with the full link on the right but the user does not want it that way, that is why I am trying to assign the hyperlink to the report no variable.

Re: assigning hyperlink to a variable

Reeza — Mon, 10 Sep 2018 19:02:36 GMT

I get that. Can you post some fully reproducible code to see what you're getting? I can't run your code because no data. Mocking up some data would help with testing.

The only difference between Cynthia's code and yours that I see is that link is already a variable and she creates a new one, so I wonder if that's the issue. I wonder if it's setting link to missing which is then ., which then pushes it to your working directory, which is what that path looks like.

Re: assigning hyperlink to a variable

Elliott — Mon, 10 Sep 2018 19:26:26 GMT

I kept playing with it, I have it working now.. the only thing is I would now like the report no to be in blue font with an underline so it is obvious it is a link.. how is that done? I assume it is with a style attribute of some kind.

Thanks, this is awesome

Re: assigning hyperlink to a variable

Reeza — Tue, 11 Sep 2018 17:36:54 GMT

This user answer the question here:

https://communities.sas.com/t5/ODS-and-Base-Reporting/ODS-EXCEL-Clickable-external-hyperlink/m-p/494561#M21687