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?
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.
@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 ?
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.