04-07-2018 06:06 AM
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.
Format Degree_Mod $15.;
if Degree =' ' then do;
if FINDW(Customer_Name, 'MS')>0 then do; Degree_Mod1="MS";
I am not sure if my SAS code is correct here. Can you please help?
04-07-2018 09:24 AM
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.
04-07-2018 02:25 PM
@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 ?
04-07-2018 03:52 PM
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;