BookmarkSubscribeRSS Feed
Calcite | Level 5

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

Super User

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?

Calcite | Level 5

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."")";


%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);






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



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg



Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 3 in conversation