BookmarkSubscribeRSS Feed
John_Papproth
Calcite | Level 5

I am working with text miner and sas visual analytics.

I am scoring the text based on sentiment topics consisting of terms and phrases.

I would like to use SAS VA to allow users to review the results and submit changes via comments.

 

To make the review process easier it was suggested that I colorize the text.

I have worked up an example using Microsoft Excel.

I have a block of text within a row that I would like to colorize based on terms that exist in another column.

 

In the example, column B2 contains a block of text, C2 contains positive terms, and D2 contains negative terms.

I want to locate the positive terms (C2) within the text (B2) and change the font color to green,

locate the negative terms (D2) within the text (B2) and change the font color to red.

In EXCEL I can do this with VBA.

 

How can I do this within SAS VA?

 

I could not attach a macro worksheet, so I am including the code here.

VBA code:

Option Explicit


Sub colorTerms()
'
' colorTerms Macro
'
'
    Dim txtTerms
    Dim posTerms
    Dim negTerms
    Dim txt
    Dim r
    Dim i
    Dim posArray
    Dim negArray
    Dim s
    r = 1
    Do
        r = r + 1
        If Cells(r, 1) = "" Then Exit Do
        txtTerms = Cells(r, 2)
        posTerms = Cells(r, 3)
        negTerms = Cells(r, 4)
        posArray = Split(posTerms, ",")
        negArray = Split(negTerms, ",")
        For Each txt In posArray
            s = 1
            Do
                i = InStr(s, UCase(txtTerms), UCase(txt))
                If i > 0 Then
                        Cells(r, 2).Characters(Start:=i, Length:=Len(txt)).Font.Color = -11489280
                        Cells(r, 2).Characters(Start:=i, Length:=Len(txt)).Font.FontStyle = "Bold"
                        s = i + Len(txt)
                Else
                    Exit Do
                End If
            Loop
        Next
        For Each txt In negArray
            s = 1
            Do
                i = InStr(s, UCase(txtTerms), UCase(txt))
                If i > 0 Then
                        Cells(r, 2).Characters(Start:=i, Length:=Len(txt)).Font.Color = -16776961
                        Cells(r, 2).Characters(Start:=i, Length:=Len(txt)).Font.FontStyle = "Bold"
                        s = i + Len(txt)
                Else
                    Exit Do
                End If
            Loop
        Next
    Loop
End Sub


Sub reset_colorTerms()
    Range("B2").Select
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    Selection.Font.Bold = True
    Selection.Font.Bold = False
    Range("B1").Select
End Sub

 

1 REPLY 1
RichardPaterson
Obsidian | Level 7

I don't think there is a way to do this with standard VA functionality. However Base SAS can do this very easily. Are your familiar with Stored Processes? A stored process could replicate this process, outputing the results directly into VA. 

 

Richard

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 1253 views
  • 0 likes
  • 2 in conversation