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.


Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.


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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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