ODS EXCEL Clickable external hyperlink

[Dick_Pickett]

If a hyperlink is in a separate cell without any other text, you can double-click on the cell, then click on a different cell, to turn the link into an active hyperlink. I want the hyperlink to be clickable directly from ODS EXCEL without going in and out of the cell in Excel.

Is this possible in ODS Excel?

Here is test code. 

PROC TEMPLATE ;
define style styles.mystyle ;
parent=styles.excel ;
CLASS HEADER, DATA, DATAEMPHASIS, LINECONTENT / borderstyle=hidden fontsize=12pt font_face=Times ;
CLASS DATA / background=GhostWhite ;
END ;
RUN;

ODS escapechar='~' ;

/* Enter report text */
DATA text;
LENGTH d1 $ 255;
d1="~S={font_weight=bold FONT_STYLE=italic}CONTENTS"; OUTPUT;
d1="This workbook contains information on your SNF's performance in the first year of the SNF VBP Program, including the incentive"; OUTPUT;
d1="payment multiplier that will be applied to your SNF's Medicare fee-for-service claims from October 1, 2018, through September 30,"; OUTPUT;
d1 ="2019 (fiscal year [FY] 2019)."; OUTPUT;
d1 =""; OUTPUT;
d1="~S={font_weight=bold}Tab 2~S={}~_of this workbook contains a complete list of stays used to calculate your SNF's performance period (CY 2017) risk-standardized"; OUTPUT;
d1 ="readmission rate (RSRR). Patients admitted to SNFs between January 1, 2017, and December 31, 2017, are included in this workbook."; OUTPUT;
d1 ="SNFs have an opportunity to review and submit corrections to their CY 2017 RSRR until March 31, 2019; this constitutes Phase One"; OUTPUT;
d1 ="of the SNF VBP Review and Corrections process. Any errors in patient stay or patient identifying information must be corrected by the"; OUTPUT;
d1 ="SNF or admitting hospital using the established claims process. Correction requests concerning CY 2017 RSRRs may be emailed to"; OUTPUT;
d1 ="SNFVBPinquiries@cms.hhs.gov with the subject line 'SNF VBP Review and Corrections Inquiry' along with the following information:"; OUTPUT;
d1 ="your SNF's CMS Certification Number (CCN), your SNF's name, the correction request, and the reason for requesting the correction."; OUTPUT;
d1 =""; OUTPUT;
d1 ="~S={font_weight=bold}Tab 3~S={}~_of this workbook contains facility-level data, including your SNF's performance score, rank, and incentive payment multiplier for"; OUTPUT;
d1 ="the FY 2019 Program Year. SNFs have an opportunity to review and submit corrections to their SNF VBP performance score and"; OUTPUT;
d1 ="program rank only up to 30 days following this report being made available; this constitutes Phase Two of the SNF VBP Review and"; OUTPUT;
d1 ="Corrections process. Correction requests may be emailed to SNFVBPinquiries@cms.hhs.gov with the subject line 'SNF VBP Review"; OUTPUT;
d1 ="and Corrections Inquiry' along with the following information: your SNF's CCN, your SNF's name, the correction request, and the"; OUTPUT;
d1 ="reason for requesting the correction."; OUTPUT;
d1 =""; OUTPUT;
d1 ="SNF VBP performance scores are based on the Skilled Nursing Facility 30-Day All-Cause Readmission Measure (SNFRM), a quality"; OUTPUT;
d1 ="measure that assesses rates of unplanned hospital readmissions for Medicare fee-for-service beneficiaries within 30 days of discharge"; OUTPUT;
d1 ="from a prior proximal hospital stay. The SNFRM is risk-adjusted to account for various patient-level risk factors, such as clinical and"; OUTPUT;
d1 ="demographic characteristics."; OUTPUT;
d1 =""; OUTPUT;
d1 ="~S={font_weight=bold }Tab 4~S={}~_of this workbook contains a Data Dictionary with additional information on the data in~S={}~_~S={font_weight=bold}Tabs 2~S={}~_and~S={}~_~S={font_weight=bold}3~S={}."; OUTPUT;
d1 =""; OUTPUT;
d1="~S={font_weight=bold FONT_STYLE=italic}OTHER REMINDERS"; OUTPUT;
d1 ="~S={font_weight=bold color=red}As a reminder, please do not email protected health information or other sensitive patient information, as it will constitute"; OUTPUT;
d1 ="~S={font_weight=bold color=red}a Health Insurance Portability and Accountability Act (HIPAA) violation. If you are not sure of whether the information"; OUTPUT;
d1 ="~S={font_weight=bold color=red}you want to email is identifiable, please contact your institution's Privacy Officer before emailing your correction request."; OUTPUT;
d1 =""; OUTPUT;
d1 ="Your facility's Annual Performance Score Report is a read-only document, which prevents users from making unintentional changes."; OUTPUT;
d1 ="To make changes to the file, use the File\Save As option to save the document with a different filename."; OUTPUT;
d1 =""; OUTPUT;
d1 ="If you have questions about the contents of your workbook, please e-mail the SNF VBP Help Desk at"; OUTPUT;
d1 ="~{style [textdecoration=underline foreground=blue url='mailto:SNFVBPinquiries@cms.hhs.gov']SNFVBPinquiries@cms.hhs.gov}"; OUTPUT;
d1 =""; OUTPUT;
d1 ="If you would like to learn more about the SNF VBP Program, please visit the Centers for Medicare & Medicaid Services (CMS) website:"; OUTPUT;
d1 ="~{style [textdecoration=underline foreground=blue url='https://goo.gl/G4a0A9']https://www.cms.gov/Medicare/Quality-Initiatives-Patient-Assessment-Instrumen... } ~{newline} " ; OUTPUT;
RUN;

ODS ESCAPECHAR='~';

ODS EXCLUDE NONE; /* Resume output to open destinations */
ODS RESULTS OFF;
ODS LISTING CLOSE; /* Close Listing */
ODS EXCEL FILE="&outpath.TEST.XLSX"
STYLE=mystyle
TITLE='SNF VBP Quarterly Confidential Feedback Report - June 2017'
AUTHOR='RTI International'
OPTIONS (SHEET_INTERVAL = 'PROC'
Sheet_Name = "1 - Cover Sheet"
Orientation = 'Landscape'
page_order_across = 'Yes'
suppress_bylines = 'Yes'
pages_fitwidth = '1'
FitToPage = 'Yes'
Center_Horizontal = 'Yes'
Center_Vertical = 'Yes'
embed_footnotes_once = 'On'
embedded_footnotes = 'On'
embed_titles_once = 'On'
embedded_titles = 'On'
gridlines = 'Off'
tab_color = "%SCAN(&Color,1)"
start_at = '2,1'
Absolute_Column_Width = '1,126'
ABSOLUTE_ROW_HEIGHT = '18'
);
TITLE1 FONT=TIMES BOLD H=12pt "Skilled Nursing Facility Value-Based Purchasing (SNF VBP) Program";
TITLE2 FONT=TIMES BOLD H=12pt "Annual Performance Score Report";
TITLE3 FONT=TIMES BOLD H=12pt "Performance Period: Calendar Year (CY) 2017";
TITLE4 FONT=TIMES BOLD H=12pt "Baseline Period: Calendar Year (CY) 2015";
TITLE5;
TITLE6 FONT=TIMES BOLD H=12pt "Provider Name: %NRBQUOTE(test)";
TITLE7 FONT=TIMES BOLD H=12pt "Provider CCN: %NRBQUOTE(test)";
FOOTNOTE1 BOLD COLOR=RED FONT=TIMES ITALIC H=18pt JUSTIFY=C "CONFIDENTIAL";
PROC REPORT DATA=text NOWD
style(header COLUMN)=[width=100%];
columns d1 ;
define d1 / '' style(COLUMN)=[font=(Times, 12pt) cellwidth=10in width=1000%];
RUN;
QUIT;
TITLE1;
TITLE2;
TITLE3;
TITLE3;
TITLE4;
TITLE5;
TITLE6;
TITLE7;
FOOTNOTE1;
/* ----------------------------------------------------------------- */
ODS _ALL_ CLOSE ;
QUIT ;
RUN ;
/* ----------------------------------------------------------------- */
ODS EXCEL CLOSE ; %*<-- Close the tagset and output reports ;
ODS RESULTS ; %*<-- Turn traditional SAS output back on ;
ODS LISTING ;
/* ----------------------------------------------------------------- */
/* Reset titles and footnotes */
TITLE1;
TITLE2;
TITLE3;
TITLE4;
TITLE5;
TITLE6;
TITLE7;
FOOTNOTE1;
FOOTNOTE2;
FOOTNOTE3;

[SuryaKiran]

Directly give excel formula in your SAS Dataset.

Example:

data class;
set sashelp.class;
Display_Link='=HYPERLINK("https://communities.sas.com/","SAS Community")';
run;


  ods excel file="/usr/apps/sasdata/CAO/CAOSec/local_access/CAO_DropBox/Kiran/test/hyperlink.xlsx" style=statistical
      options( suppress_bylines='yes' sheet_interval='none' );
proc print data=class;
  run;

  ods excel close;

Similar post here

[Dick_Pickett]

   /*Clickable after double clicking the cell, going out and coming back into the cell.*/
    d1 ="~{style [textdecoration=underline foreground=blue url='https://goo.gl/G4a0A9']https://www.cms.gov/Medicare/Quality-Initiatives-Patient-Assessment-Instrumen... }" ; OUTPUT;

/* We found a problem with some content in TEST.XLSX. Do you want us to try to recover as much as we can?
   Removed Records: Formula from /xl/worksheets.sheet1.xml part
*/
    d1='=HYPERLINK("https://goo.gl/G4a0A9",^{style [textdecoration=underline foreground=blue]"https://www.cms.gov/Medicare/Quality-Initiatives-Patient-Assessment-Instruments/Value-Based-Programs...}")' ; OUTPUT ;

/* Clickable URL, but no color/underline */
    d1='=HYPERLINK("https://goo.gl/G4a0A9","https://www.cms.gov/Medicare/Quality-Initiatives-Patient-Assessment-Instrume...")' ; OUTPUT ;

[Dick_Pickett]

   /* Clickable after double clicking the cell, going out and coming back into the cell.
     http://support.sas.com/kb/41/613.html

     Option 1
         Double-click the hyperlink cell that contains the web address.
         Click any other cell in the worksheet. This activates the hyperlink.
   */
    d1 ="~{style [textdecoration=underline foreground=blue url='https://goo.gl/G4a0A9']https://www.cms.gov/Medicare/Quality-Initiatives-Patient-Assessment-Instrumen... }" ; OUTPUT;

/* Clickable URL, but no color/underline
      Option 2
         If you have multiple rows to make available, create a new column in Excel with a formula that uses a hyperlink similar to this =HYPERLINK(cell number)
   =HYPERLINK(“[http:// example.xxx.com/budget.xls]Monthly!B49”, “Click to See the Dept Detail”).
*/
    d1='=HYPERLINK("[https://goo.gl/G4a0A9]!B49", "https://www.cms.gov/Medicare/Quality-Initiatives-Patient-Assessment-Instruments/Value-Based-Programs...")' ; OUTPUT ;

I'm looking for a 3rd option that will allow ="~{style [textdecoration=underline foreground=blue from option 1 to be applied to option 2.

[Reeza]

Have you tried using PROC REPORT in conjunction with the STYLE options?

[Dick_Pickett · Accepted solution]

Solution

      compute d1 ;
            IF d1='=HYPERLINK("[https://goo.gl/G4a0A9]", "https://www.cms.gov/Medicare/Quality-Initiatives-Patient-Assessment-Instruments/Value-Based-Programs...")' THEN DO;
               CALL define(_col_,'style','style=[textdecoration=underline foreground=blue]');
            END;

[Reeza]

FYI - if you put the code in code blocks it's easier to read and follow your posts/problems.