topic ods tagsets excelxp hyperlink variable in SAS Procedures

ods tagsets excelxp hyperlink variable

npatel_01 — Sat, 21 Dec 2019 03:17:50 GMT

I am using ods tagsets excelxp to output excel sheets. I would like to avoid using proc report because I already have the rest of the code written up without it. I have two issues:

1. I need to create a variable where the values hyperlink to specific webpages but the display is still that of the original variable. So for example, if the variable was gender, then I want the values to show as M/F but if clicked, it would hyperlink to a specific webpage.

2. the url is a combination of other variable values. so say that I have variables called webpage and pageno so then the url would be www.webpage.com/pageno . I have about 4-5 such variables that are put into the url but I can't get them to resolve right and get the correct url at the end.

Any help would be appreciated!

Re: ods tagsets excelxp hyperlink variable

ballardw — Mon, 23 Dec 2019 17:55:33 GMT

Can you show what you have attempted?

You say you don't want Proc Report. So how do you want to display the values? Proc Print? Tabulate? Something else?

Re: ods tagsets excelxp hyperlink variable

Cynthia_sas — Mon, 23 Dec 2019 21:07:58 GMT

Hi:
My guess is that you'll have to build your URL variable outside of your procedure. I assume that if you are creating a URL, that you are using either PRINT or TABULATE if you're not doing REPORT. My guess is that a STYLE override with PROC PRINT or TABULATE would work, but you'd also need a user-defined format to make it work.
Cynthia

Re: ods tagsets excelxp hyperlink variable

npatel_01 — Fri, 10 Apr 2020 18:15:58 GMT

Say the link is a combination of the site name and page no that are based off of existing variables form and id.

I have a macro that makes and outputs the link variable and I use the HYPERLINK function to embed the link into the display_link variable but have it print as the form name.

I apologize that the code is a mess, I've tried so many things that they've jumbled into one.

%macro link(lib, dat);
    proc sql no print;
    select near into :numvars
      from dictionary.tables
     where libmame="&LIB" and memname = "&DAT";
  
   proc datasets library=&LIB;
     modify &DAT;
    %do i=1 %to &numvars.;
 
   call symputx('form', put(form, $35.), 'G');
   call symputx('id', put(pid, $5.), 'G');

   Display_Link="=HYPERLINK(""https://&form.com/&id."", ""&form."")";
output;
%end;

run;
%mend;

%link(work, data);

ods tagsets.excelxp file=data.xls style=custonmjournal options(sheet_name='all IDs' row_repeat=1 ABSOLUTE_COLUMN_WIDTH = '10, 5' orientation='landscape frozen_headers='YES');

proc print data=data noobs label split'/'
  style(column) = {fontsize=8pt}
  style(header) = {fontsize=8py just=l};
  var form id;

  compute id;
  urlstring= Display_Link;
  call define(_col_, 'URL', urlstring);
endcomp;
run;

Re: ods tagsets excelxp hyperlink variable

Cynthia_sas — Sat, 11 Apr 2020 17:22:26 GMT

Hi:
It is nearly impossible to comment constructively without having data to use. The only comment I can make is that
1) you don't show complete code -- where is your ending ODS TAGSETS.EXCELXP CLOSE?
2) Your FILE= option should be a quoted string;
3) Nobody can even test your code with fake data because you're using a custom style that you do not provide
4) You can't use a COMPUTE block with PROC PRINT -- only with PROC REPORT.

Also, it's not a good idea to insert a new posting into a post that was created in a different year. It's better to start a new post for a new problem and then to refer to the original 2019 post if it's relevant.

Perhaps if you start a new post and include ALL your code and ALL your data, others will be able to help you.
Cynthia