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
... View more