CODEWITHSUNDEEP
excelhyperlinksasods
chezleon
chezleon

Reputation: 29

Clikable Hyperlink with SAS ODS EXCEL

I got a problem with Hyperlink generated by SAS ODS EXCEl. I'm using SAS9.4TM3 and EXCEL 2013.

I coded this

data lst_tie;
NUM_TIE = '2900004227803';
output;
NUM_TIE = '2900004233852';
output;
run;
data lst_tie(drop=HL);
set lst_tie;
format HL2 $500.;
HL = "http://tier-kh.cm-cic.fr/tie6_tiers/default.aspx?trt=tiesyn&banque=02297&caisse=38848&tiers="||NUM_TIE;
HL2 = '=LIEN_HYPERTEXTE("'||HL||'";"'||NUM_TIE||'")';
run;

ods excel file = "$GRPFPU/test_tiesyn.xlsx"
    options (absolute_column_width="3cm,20cm,20cm");
proc report data=lst_tie
;
column  NUM_TIE   
        HL2;
define num_tie / "Numero" style(column)={ width=100%};
define HL2  / "Tiers" style(column)={tagattr='wraptext:no' width=100%};
quit; 
ods excel close;

The URL seems well encoded :

=LIEN_HYPERTEXTE("http://tier-kh.cm-cic.fr/tie6_tiers/default.aspx?trt=tiesyn&banque=02297&caisse=38848&tiers=2900004227803";"2900004227803")

without carriage return (CR).

But, on opening the XLSX file there is a CR characters just after LIEN_HYPERTEXTE (HYPERLINK in English)

XLSX Preview 1

But if I delete the CR so the hyperlink is OK.

XLSX OK

I tried several option as WIDTH_COLUMS, Wrap Option , but no way.

Thanks

Upvotes: 0

Views: 1667

Answers (2)

chezleon
chezleon

Reputation: 29

To have a clickable hyperlink I add a format ``

data lst_tie;
    NUM_TIE = '2900004227803';
    output;
    NUM_TIE = '2900004233852';
    output;
run;

data lst_tie;
    set lst_tie;
    format HL2 $500.;
    HL = "http://tier-kh.cm-cic.fr/tie6_tiers/default.aspx?trt=tiesyn&banque=02297&caisse=38848&tiers="||NUM_TIE;
run;

data one;
    set lst_tie;
    retain fmtname '$urltie';
    rename NUM_TIE=start;
    label =  HL;
run;

proc format cntlin=one;
run;

ods excel file = "$GRPFPU/test_tiesyn.xlsx"
    options (absolute_column_width="3cm,20cm,20cm" flow="tables");

proc report data=lst_tie
;
    column  NUM_TIE   
    ;
    define num_tie / "Numero" style(column)={TAGATTR='format:0' width=1.5in url=$urltie. color=cx0000FF textdecoration=underline /*tagattr='wraptext:no' width=100%*/
    };
quit;

ods excel close;

``

Upvotes: 1

Tom
Tom

Reputation: 51611

ODS EXCEL is trying to make your printout pretty by inserting physical line breaks into long lines. Apparently it doesn't notice that your value is a formula instead of plain text.

Starting with SAS 9.4M4 you can add flow="tables" to the ODS statement. See this SAS Blog post

ods excel file = "$GRPFPU/test_tiesyn.xlsx"
    options (absolute_column_width="3cm,20cm,20cm"
             flow="tables"
            )
;

For older versions of SAS, like yours, try making the column wider so it doesn't try to wrap it. Try adding width=1000% instead of width=100% to the column with the links.

define HL2  / "Tiers" style(column)={tagattr='wraptext:no' width=1000%};

Upvotes: 1

Related Questions