Addind hyperlinks to xlsx file

[SASdevAnneMarie]

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

[ghosh]

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/5791... 

[mklangley]

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-formul...

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;

[SASdevAnneMarie]

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!

[mklangley]

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.

[SASdevAnneMarie]

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

[mklangley · Accepted solution]

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.

[SASdevAnneMarie]

Thank you!
That works!

[SASdevAnneMarie]

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

[ballardw]

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.