topic Re: PROC SQL: REMOVING MIDDLE INITIAL FROM STRING in SAS Programming

PROC SQL: REMOVING MIDDLE INITIAL FROM STRING

PhatRam33 — Fri, 17 Feb 2017 16:05:30 GMT

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.

Re: PROC SQL: REMOVING MIDDLE INITIAL FROM STRING

collinelliot — Fri, 17 Feb 2017 16:58:34 GMT

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

Re: PROC SQL: REMOVING MIDDLE INITIAL FROM STRING

art297 — Fri, 17 Feb 2017 17:10:59 GMT

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

Re: PROC SQL: REMOVING MIDDLE INITIAL FROM STRING

Peter_C — Fri, 17 Feb 2017 17:48:24 GMT

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 ;

Re: PROC SQL: REMOVING MIDDLE INITIAL FROM STRING

PhatRam33 — Fri, 17 Feb 2017 21:26:59 GMT

Thank you all for the help! That did the trick!

Re: PROC SQL: REMOVING MIDDLE INITIAL FROM STRING

PGStats — Fri, 17 Feb 2017 21:46:31 GMT

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.