Excel XP and Hyperlink

[sks]

Hi All,

     I am using ODS and ExcelXP tagset to create a report which has a summary and a detail tab in excel. The detail tab is grouped by age ranges and the summary tab contains the summary of all age ranges. Is there a way to create a hyperlink in the summary page for the age range column, when the user clicks on the age range, it'll take them to the details for that age range in the same file, but on the detail tab. The user does not want to have a tab for each age range, so using the sheet_interval= 'bygroup' is not an option I can use.

I appreciate any suggestions on how to accomplish the hyperlinks.

Thank You

Shri

[Cynthia_sas]

Hi,

This is do-able, using the URL style attribute. Not on a computer with SAS right now, so tested example is not possible but generally you need to

1) find out proper link format for XML file. To do this, you can make (without SAS) a simple two- sheet workbook and build links from the first sheet to the second sheet.

2) then save workbook as excel 2003 XML and close file

3) open file with Notepad and look for constructed URL. It will probably be something like

Ss:HREF="c:\temp\fname.XML#sheet2!a12"  (I may be misremembering the placement of the # and !)

4) once you have the URL down, build your user-defined format in sas, something like:

Proc format;

  Value theurl 11='c:\temp\fname.XML#sheet2!a12'

                      12='c:\temp\fname.XML#sheet2!a26';

* would have 1 URL for every age;

Run;

5) now, use the format as a style override:

ODS TAGSETS . Excelxp file='my file.xml';

Proc report data=sashelp.class nowd;

  Column age height weight;

  Define age/group

            Style(column)={URL=theurl.};

  Define height/mean;

  Define weight/mean;

Run;

ODS TAGSETS.excelxp options(sheet_interval='none');

Proc report data= sashelp.class nowd;

Column name age sex height weight;

By age;

Run;

ODS TAGSETS.excelxp close;

6) test and adjust the cell references in the URL as needed.

OR, alternately, investigate the contents= suboption and see whether that will work for you instead.

Cynthia

[sks]

Cynthia,

       Thank you for the response, this will work when I know exactly what the destination cell is

for eg.

    Value theurl 11='c:\temp\fname.XML#sheet2!a12'

                      12='c:\temp\fname.XML#sheet2!a26';

What if I do not know the exact destination cell, but I'll know what my destination value is going to be

like Age 11, Age 12...., ( the value age 11 can be in cell a12 or 13 depending on the data)

can we build URL string knowing the value and not necessarily the exact cell.

Thank You

Shri

[Cynthia_sas]

Hi:

  If I needed to dynamically set the cell reference, I would probably use a SAS Macro program to do it. Generally, I would use PROC FREQ or PROC MEANS to give me the number of obs in every "group" and then figure out whether I have titles in or out of the sheet, whether I have by lines in or out of the sheet, and also account for the extra empty cells between tables . Given that the output from TAGSETS.EXCELXP always starts in A1, and you know (from PROC FREQ) that the first group has 10 obs and you know that your output has a summary row and a title row, that means that the second group should start around A12 or A13.

  With SAS Macro processing and a DATA step program, you can build the PROC FORMAT code dynamically using the CNTLIN capability to read a dataset to seet the value of the formats.

  Depending on the "fudge" factor that you add to the number of obs in each group, you might still be off by a few cells, but this technique will get you navigated to a cell in the vicinity of the group you want.

  Otherwise, it'll be an iterative process to get it -exactly- right. I guess it sort of depends on how picky your users are and how much extra programming you want to do. At this point, having a separate tab for every by group seems easier to me for navigation purposes and for programming purposes, because each tab is limited to the group and only the group and there is no guesswork or extra programming involved. If you don't mind the extra programming and your users are insistent on having all the by groups in one sheet, then this is do-able, but with more work on your part.

cynthia