Using PROC SQL, is it possible to remove the Middle Initial (if it is present) from the created First_Name variable in the SQL below?
Example:
Smith, John M
SQL below returns
Smith, John M
John M
Smith
Need to remove the middle initial 'M' where present from first_name
Here is what I have so far:
Select
FULL_NAME
,scan(FULL_NAME,-1,',') as first_name
,scan(FULL_NAME,1,',') as last_name
FROM
SAMPLE_TABLE
Thanks in advance.
Your code was close:
data sample_table; informat full_name $50.; input full_name &; cards; Smith, John M Smith, Joe Smith, Mary Margaret Smit, Margaret M ; proc sql; create table want as Select FULL_NAME, scan(FULL_NAME,1,' ,') as last_name, scan(FULL_NAME,1-countw(full_name),' ,') as first_name from SAMPLE_TABLE ; quit;
HTH,
Art, CEO, AnalystFinder.com
It depends on how consistently the names are formatted but you could try a regex. The below is not tested, but the idea to find a space followed by a single character and remove it before using the scan.
scan(prxchange('s/\s\w$//', 1, strip(FULL_NAME)), -1, ',') as first_name
Your code was close:
data sample_table; informat full_name $50.; input full_name &; cards; Smith, John M Smith, Joe Smith, Mary Margaret Smit, Margaret M ; proc sql; create table want as Select FULL_NAME, scan(FULL_NAME,1,' ,') as last_name, scan(FULL_NAME,1-countw(full_name),' ,') as first_name from SAMPLE_TABLE ; quit;
HTH,
Art, CEO, AnalystFinder.com
proc sql ;
create table result as
select scan( source,1,',' ) as surname length=12
, case when length( scan( source, -1, ' ' ) ) > 1
then scan( source, -1, ' ' )
when length( scan( source, -2, ' ' ) ) > 1
then scan( source, -2, ' ' )
else scan( source, -3, ' ' )
end as forename length= 12
from source_data
;
quit ;
Thank you all for the help! That did the trick!
Regular expressions are most flexible
data a;
name = "George W. Bush"; output;
name = "George W Bush"; output;
name = "George Bush"; output;
run;
proc sql;
select name,
prxChange("s/(\w+\s)(\s*\w\.?\s+)(\w+)/\1\3/io",1,name) as shortName
from a;
quit;
Remember: prxChange returns the original string when the pattern is not found.
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.