topic Re: Dynamically Creating a Send to Excel URI in Developers

Dynamically Creating a Send to Excel URI

DavidPhillips2 — Wed, 25 Mar 2015 18:56:34 GMT

In SAS Information Delivery Portal 4.2 M2 using SAS 9.2 I am trying to feed

%let link2 = href="https:// myServerName.vcu.edu/SASPortal/Director?_directive=STPRun&_action=execute&_program=%2FUsers%2Fphillipsdl%2FMy%20Folder%2FEnrollment%20Tab"&FORMAT="EXCEL" target=_blank> <img src='https://sites.google.com/site/dashboardsamples/_/rsrc/1340285950455/home/export-icon.gif' alt='Export to Excel' title='Export to Excel' height='15' width='15' style='border:none;' /></a>

PUT "<td align ='right'>&link2</td>";

And receive

957 "<td align ='right'><a

The SAS System

_______________________ __________________

49 557

957 !

ERROR: DATA STEP Component Object failure. Aborted during the COMPILATION phase.

NOTE 49-169: The meaning of an identifier after a quoted string may change in a future SAS release. Inserting white space between a quoted string and the succeeding identifier is recommended.

ERROR 557-185: Variable myServerName is not an object.

The original author of the code

%LET SERVER = https://&_SRVNAME;

%LET COMMAND = %nrstr("/SASPortal/Director?_directive=STPRun&_action=execute&_program=");

%LET SP = %SYSFUNC(URLENCODE(&_PROGRAM));

%LET SELF_URL=&SERVER.&COMMAND.&SP;

%LET SP_NAME=%SYSFUNC(SCAN(&_PROGRAM,-1,'/'));

%LET PORTLET_TITLE = %SYSFUNC(URLENCODE(&SP_NAME));

%LET EXCEL = %nrstr("&FORMAT=")EXCEL;

%LET SELF_URL_EXCEL = &SELF_URL.&EXCEL;

%LET HELP_SP = %2FReports%2FResearch+Dashboard%2FResearch+Dashboard+Help;

%LET HELP_PARAM = %nrstr("&Portlet_Title=");

%LET HELP_URL = &SERVER.&COMMAND.&HELP_SP.&HELP_PARAM.&PORTLET_TITLE;

%LET link2=<a href="&SELF_URL_EXCEL" target=_blank><img src='https://sites.google.com/site/dashboardsamples/_/rsrc/1340285950455/home/export-icon.gif' alt='Export to Excel' title='Export to Excel' height='15' width='15' style='border:none;' /></a>;

FOOTNOTE1 J=L " &link2";

Used this block to create a functional send to Excel button as part of a footnote. Because of the way my I am using HTML on my page I cannot use a SAS footnote but instead need to use the Put “Insert html here” method.

Is there a reason why the server name after // is not being handled the way I am aiming for? I am thinking this has to do with special characters so I played with %nrstr()

Re: Dynamically Creating a Send to Excel URI

Quentin — Fri, 27 Mar 2015 01:48:55 GMT

Hi,

I see a few potential issues. Your double quotes look odd to me. Are you trying to pass a parameter named format with a value of "EXCEL"? It's probably easier to pass the value without quotes. Also note you have a space after the https:// that you probably don't want. Sometimes it is easier to try hard coding it, i.e. without the macro variable, just to get the basic code right. Then you can try doing it with a macro variable and wrestling with the macro quoting as a second step.

I would try something like:

PUT "<td align ='right'><a href='https://myServerName.vcu.edu/SASPortal/Director?_directive=STPRun&_action=execute&_program=%2FUsers%2Fphillipsdl%2FMy%20Folder%2FEnrollment%20Tab&FORMAT=EXCEL' target=_blank> <img src='https://sites.google.com/site/dashboardsamples/_/rsrc/1340285950455/home/export-icon.gif' alt='Export to Excel' title='Export to Excel' height='15' width='15' style='border:none;' /></a></td>";

Re: Dynamically Creating a Send to Excel URI

DavidPhillips2 — Wed, 01 Apr 2015 13:50:56 GMT

I’m having a hard time debugging the URL link because there are a number of potential scenarios that could be causing the error.

It looks like I need to use nrstr around &Format. Should I enclose only &format with nrstr or enclose the whole link with nrstr?

Should I be using a link format with %F for slashes or with /.

&format is a variable the original author used to tell the stored process to display the content type as Excel, PDF, HTML.

Re: Dynamically Creating a Send to Excel URI

Kurt_Bremser — Wed, 01 Apr 2015 14:10:01 GMT

Your link2 macro variable contains double quotes. When the macro processor replaces &link2 within a string you already quoted with double strings, you create several quoted strings with unquoted text in between, which causes the error.

My solution to this problem is to build complex html text with several put statements that use the proper quotes so that SAS is not confused:

put '<a href="'@;

put "&link2"@;* or put trim(link2)@; if it is in a datastep variable;

put '">';

Re: Dynamically Creating a Send to Excel URI

DavidPhillips2 — Wed, 01 Apr 2015 18:19:36 GMT

This worked it is still in pieces but the basic idea is here.

%LET _ODSDEST=Tagsets.HTMLPANEL;

%LET _ODSOPTIONS=gtitle gfootnote options(panelcolumns='4') style=normal;

%LET SERVER = https://&_SRVNAME;

%LET COMMAND = %nrstr(/SASPortal/Director?_directive=STPRun&_action=execute&_program=);

%LET SP = %SYSFUNC(URLENCODE(&_PROGRAM));

%LET SELF_URL=&SERVER.&COMMAND.&SP;

%LET SP_NAME=%SYSFUNC(SCAN(&_PROGRAM,-1,'/'));

%LET PORTLET_TITLE = %SYSFUNC(URLENCODE(&SP_NAME));

%LET MAX = %nrstr("&FORMAT=")MAXIMIZE;

%LET EXCEL = %nrstr("&FORMAT=")EXCEL;

%LET PDF = %nrstr("&FORMAT=")PDF;

%LET SELF_URL_MAX = &SELF_URL.&MAX;

%LET SELF_URL_EXCEL = &SELF_URL.&EXCEL;

%LET SELF_URL_PDF = &SELF_URL.&PDF;

%LET HELP_SP = %2FReports%2FResearch+Dashboard%2FResearch+Dashboard+Help;

%LET HELP_PARAM = %nrstr("&Portlet_Title=");

%LET HELP_URL = &SERVER.&COMMAND.&HELP_SP.&HELP_PARAM.&PORTLET_TITLE;

PUT "<a href=";

PUT "&SERVER/SASPortal/Director?_directive=STPRun&_action=execute%nrstr(&_program)=%SYSFUNC(URLENCODE(&_PROGRAM))%nrstr(&FORMAT=)EXCEL";

PUT " target=_blank><img src='https://sites.google.com/site/dashboardsamples/_/rsrc/1340285950455/home/export-icon.gif' alt='Export to Excel' title='Export to Excel' height='15' width='15' style='border:none;' /></a>";