Reading Excel hyperlinks and stroring them in a new column (SAS 9.4)

[Informatician]

I have an Excel (.xlsx) file that includes a column with an embedded hyperlink to a website. I would like to be able to have sas read the hyperlink and store it as a string in a new variable. I am not sure how to accomplish this. It is not an option to just create the hyperlink text manually because the source document hyperlinks are different in each cell and the part that changes is not available in any other location at this time. Please see the attached test document.

Summary: I would like to take the hyperlink in the "patient name" var and put it into a new var called "hyperlink" as a string value.

[koyelghosh · Accepted solution]

Not sure if SAS can extract hyperlinks embedded in texts. However you can achieve the intended result easily by using simple Excel VBA for all cells in a column. Afterwards you can read and analyze in SAS.

[andreas_lds]

Can you post an extract of the data as dataset? See https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... for details.

Extracting a hyperlink should be possible with a regular expression.

[Informatician]

Unfortunately no, the data is HIPAA protected. However, I did attach a mock raw excel file to assist.

[koyelghosh]

Most likely you are good with VBA so you might have solved it by now. Just in case (and for future help to somebody) I am giving the steps below to extract the hyperlinks. 

The code that you will type, as shown in the 4th image has been largely taken from here (http://howtouseexcel.net/how-to-extract-a-url-from-a-hyperlink-on-excel). All I have done is shown you the process step by step. You can change the code to suit your need. The Code that you will type when you reach the fourth step is as below.

Sub ExtractHyperlink()
    Dim HL As Hyperlink
        For Each HL In ActiveSheet.Hyperlinks
            HL.Range.Offset(0, 1).Value = HL.Address
        Next
End Sub

 Now I am giving 6 steps in the form of consecutive 6 images. Please follow in sequence. Make sure you have a back up of the excel file on which you are going to work (just in case :-))

Step-01

Step_02

Step_03

Step_04

Step_05

Step_06

At this point, you can export it as csv and read using PROC IMPORT. The extension of this file would have changed from .xls/.xlsx to .xlsm, so EXCEL import will not work. I hope you get the idea.

Please let me know if this helped in any way.

Thank you.

[Informatician]

I did something very similar using the code below.

Function GetURL(pWorkRng As Range) As String

GetURL = pWorkRng.Hyperlinks(1).Address

End Function

Thank You Everyone!

[andreas_lds]

Sorry but I can't open excel files at all.

[koyelghosh]

Oh! I have more respect now for the problem you are facing. Time to ask an expert.