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

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

saiksrini — Sat, 07 Apr 2018 10:06:32 GMT

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?

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

Astounding — Sat, 07 Apr 2018 13:24:20 GMT

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.

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

Shmuel — Sat, 07 Apr 2018 18:25:54 GMT

@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 ?

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

SuryaKiran — Sat, 07 Apr 2018 19:52:49 GMT

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;