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
