topic Re: Addind hyperlinks to xlsx file in SAS Programming

Addind hyperlinks to xlsx file

SASdevAnneMarie — Fri, 24 Jul 2020 14:04:09 GMT

Hello,

In my data file I have a variable which contains the hypelinks https://xxxx.com/

I would like to export this varaible with proc export and have the ACTIVE hyperlink in my file.

Is it possible with SAS proc export? I can't find the option that can do that.

Thank you very much for your help!

Best regards,

Marie

Re: Addind hyperlinks to xlsx file

ghosh — Fri, 24 Jul 2020 14:48:18 GMT

Instead of using proc export try this Proc report solution using ods excel posted by @Cynthia_sas

https://communities.sas.com/t5/ODS-and-Base-Reporting/Hyperlink-from-ODS-Excel-to-external/td-p/579186

Re: Addind hyperlinks to xlsx file

mklangley — Fri, 24 Jul 2020 15:24:15 GMT

Here's an example using ODS EXCEL:

data have ;
    input clickable_link $ url $40.;
    datalines ;
    Google http://google.com
    SAS https://www.sas.com/en_us/home.html
    ;
run ;

ods excel file="/my_path/ods_excel_output.xlsx";
 
proc report data=have nowd;
    columns url clickable_link ;
    compute clickable_link;
        call define(_col_,'url',url );
        call define(_col_,'style','style={textdecoration=underline color=blue}');
    endcomp;
run ;
 
ods excel close;

Using the method found here: https://communities.sas.com/t5/ODS-and-Base-Reporting/Need-help-with-creating-clickable-Excel-formulas-links/td-p/244796

Or also see this slightly less convenient approach using PROC EXPORT. (It works, but you need to double click on the link's cell to activate the hyperlink first.) https://support.sas.com/kb/41/613.html

data temp1;
length mylink $ 50;
input mylink $;
datalines;
http://www.sas.com
;

proc export data=temp1 outfile='/my_path/proc_export_output.xlsx'
dbms=xlsx;
sheet='mylinks';
run;

Re: Addind hyperlinks to xlsx file

ballardw — Fri, 24 Jul 2020 15:21:31 GMT

Why not to use Export. Proc Export is concerned with values of data only. No appearance options, no links, no summaries, no border controls, no row controls. Just data. Since to have a Link associated with a value would in fact mean two "values" in a single "cell" it won't do that.

Re: Addind hyperlinks to xlsx file

SASdevAnneMarie — Fri, 24 Jul 2020 18:37:06 GMT

Hello,
Thank you for youe answer!
I would like to put a hyperlink from second observation, when I do:
proc report data=have nowd;
columns url clickable_link ;
compute clickable_link;
if _N_^=1 then do;
call define(_col_,'url',url );
call define(_col_,'style','style={textdecoration=underline color=blue}');
end;
endcomp;
run ;
it doesn't work. 😞
Maybe you know some option?

Thank you very much!

Re: Addind hyperlinks to xlsx file

mklangley — Fri, 24 Jul 2020 18:50:05 GMT

If I'm understanding correctly, you want only the second observation from your data to be used? Try using monotonic() in a WHERE clause.

proc report data=have nowd;
    columns url clickable_link ;
    compute clickable_link;
        call define(_col_,'url',url );
        call define(_col_,'style','style={textdecoration=underline color=blue}');
    endcomp;
    where monotonic() = 2;
run ;

If my understanding is off-track, please include your data, code, and desired output.

Re: Addind hyperlinks to xlsx file

SASdevAnneMarie — Fri, 24 Jul 2020 18:59:28 GMT

Thank you!
I want from second to the last.
I don't want to put the hyperlink for the first observation.

Re: Addind hyperlinks to xlsx file

mklangley — Fri, 24 Jul 2020 19:04:56 GMT

If you want the first observation omitted entirely, then use:

where monotonic() ge 2;

If that's not what you want, please post your data and desired output. Otherwise I'll just be guessing on what you want.

Re: Addind hyperlinks to xlsx file

SASdevAnneMarie — Fri, 24 Jul 2020 19:29:51 GMT

Thank you!
That works!

Re: Addind hyperlinks to xlsx file

SASdevAnneMarie — Fri, 24 Jul 2020 19:33:11 GMT

Last quick question: do you, maybe, know why my hyperlinks work in Chrome and don't work in Explorer?
Thank you!