DATA Step, Macro, Functions and more

how to serach for a text and assign a value

Accepted Solution Solved
Reply
Contributor
Posts: 33
Accepted Solution

how to serach for a text and assign a value

I have a an excel which contains extra character. How to serach for a specific strung and create another column to assign those value.

 

Here is a sample example.

 

data check;

input Message$40. ID ;

cards;

this&is&the&season                                               2

chrome&is&powerful&chrome                               4

chrome&is&causing&the&Error&chrome.             3

the&service&center&is&causing&the&Error.        5

the&error&is&comming&from&IE9                        6

the &error&is&comming&from&chrome                7

chrome&is&causing&the&Error&IE9 .                  9

the&service&center&is&causing&the&Error.      10

the&error&is&comming&from&IE9                      11

the&error&is&comming&from&chrome               12

;

run;

data check2;

set check;

if Message="chrome" then flag="chorme";

if Message="IE9" then flag="IE9";

else flag="";

run;


Accepted Solutions
Solution
‎01-27-2017 09:26 AM
Contributor
Posts: 44

Re: how to serach for a text and assign a value

try using a CASE statement with LIKE in case you wanted to match any value within a string. Hope the below example sheds some light.

 

data check;
input Message$40. ID ;
cards;
this&is&the&season                                               2
chrome&is&powerful&chrome                               4
chrome&is&causing&the&Error&chrome.             3
the&service&center&is&causing&the&Error.        5
the&error&is&comming&from&IE9                        6
the &error&is&comming&from&chrome                7
chrome&is&causing&the&Error&IE9 .                  9
the&service&center&is&causing&the&Error.      10
the&error&is&comming&from&IE9                      11
the&error&is&comming&from&chrome               12
;
run;

proc sql ;
select 
case  
when Message like "%chrome%" then "chorme"
when Message like "%IE9%" then "IE9"
else "None" 
end as flag,ID
from check;
quit ;

View solution in original post


All Replies
Solution
‎01-27-2017 09:26 AM
Contributor
Posts: 44

Re: how to serach for a text and assign a value

try using a CASE statement with LIKE in case you wanted to match any value within a string. Hope the below example sheds some light.

 

data check;
input Message$40. ID ;
cards;
this&is&the&season                                               2
chrome&is&powerful&chrome                               4
chrome&is&causing&the&Error&chrome.             3
the&service&center&is&causing&the&Error.        5
the&error&is&comming&from&IE9                        6
the &error&is&comming&from&chrome                7
chrome&is&causing&the&Error&IE9 .                  9
the&service&center&is&causing&the&Error.      10
the&error&is&comming&from&IE9                      11
the&error&is&comming&from&chrome               12
;
run;

proc sql ;
select 
case  
when Message like "%chrome%" then "chorme"
when Message like "%IE9%" then "IE9"
else "None" 
end as flag,ID
from check;
quit ;
Super User
Posts: 10,500

Re: how to serach for a text and assign a value

I think you may be looking for this:

if indexw(upcase(Message),"CHROME",'&')>0 then flag="chrome";

 

Since this looks like unstructured text the upcase and the searched for word in upper case may work better if you want to find Chrome, CHROME, CHrome, cHrome and such. I recommend a similar UPCASE approach with the IE9 search as you may have folks enter ie9.

Valued Guide
Posts: 797

Re: how to serach for a text and assign a value

Waht do you want to do if both chrome and IE9 are present?

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 118 views
  • 1 like
  • 4 in conversation