How to find a word in a text variable and fill another variable if condition is met?

Reply
Regular Learner
Posts: 1

How to find a word in a text variable and fill another variable if condition is met?

Hi, I have a large dataset in which i am trying to fill blanks in a particular column based on certain criteria.

My dataset looks like below.
Customer_Name         Degree Country
Steve                           MS       USA
Dan                              MA       CAN
Edward                        M.Tech USA
Emily MS -1st                           USA
John MBA - 2nd                       USA

If Degree variable has blanks, Customer_Name will have a reference to the degree in it.
So, i want to build a logic that If Degree is blank then search Customer_Name for 'MS' and create a new variable "Degree_mod" and fill "MS" in it. In the above table, Emily and John should have entries in the new variable. Somehow i am not able build it.
data work.Daily_dump;
set work.Daily_Dump;
Format Degree_Mod $15.;
if Degree =' ' then do;
if FINDW(Customer_Name, 'MS')>0 then do; Degree_Mod1="MS";
end;
I am not sure if my SAS code is correct here. Can you please help?

Super User
Posts: 6,785

Re: How to find a word in a text variable and fill another variable if condition is met?

Posted in reply to saiksrini

I would try it this way:

 

if degree = ' ' then degree_mod1 = scan(name, -2, '- ');

 

Be sure to include both characters (dash and blank) as delimiters for the SCAN function.

Trusted Advisor
Posts: 1,837

Re: How to find a word in a text variable and fill another variable if condition is met?

Posted in reply to Astounding

@saiksrini, you wrote:   " Emily and John should have entries in the new variable. "

In your code you check for the value  "MS" but there is no "MS" in John's line but "MBA".

@Astounding proposed using SCAN() function instead checking for special value which seems more reasonable

You need to verify that DEGREE_MOD is always separated by space and/or hyphen (" -") and nothing else.

Can a name include a hyphen in it ?

 

 

Valued Guide
Posts: 597

Re: How to find a word in a text variable and fill another variable if condition is met?

Posted in reply to saiksrini

You can use some SAS function to achieve this, but first you need to know how is your data . FIND(), INDEX(), along with SCAN(), SUBSTR() etc.. can be used. 

 

Also perl expressions can be used like: 

 

/* Add all the degrees here */
%LET Degrees=/ MS | MBA | M.Tech | MA /;
data want;
set have;
if prxmatch("&Degrees",Customer_Name) then
	Degree_mod=SCAN(substr(Customer_Name,prxmatch("&Degrees",Customer_Name)),1);
run;
Thanks,
Suryakiran
Ask a Question
Discussion stats
  • 3 replies
  • 144 views
  • 0 likes
  • 4 in conversation