PROC SQL: REMOVING MIDDLE INITIAL FROM STRING

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

PROC SQL: REMOVING MIDDLE INITIAL FROM STRING

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.

 


Accepted Solutions
Solution
‎02-17-2017 04:27 PM
PROC Star
Posts: 7,363

Re: PROC SQL: REMOVING MIDDLE INITIAL FROM STRING

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

 

View solution in original post


All Replies
PROC Star
Posts: 288

Re: PROC SQL: REMOVING MIDDLE INITIAL FROM STRING

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

 

 

 

 

Solution
‎02-17-2017 04:27 PM
PROC Star
Posts: 7,363

Re: PROC SQL: REMOVING MIDDLE INITIAL FROM STRING

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

 

Valued Guide
Posts: 2,175

Re: PROC SQL: REMOVING MIDDLE INITIAL FROM STRING

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 ;
      
Contributor
Posts: 23

Re: PROC SQL: REMOVING MIDDLE INITIAL FROM STRING

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

Respected Advisor
Posts: 4,651

Re: PROC SQL: REMOVING MIDDLE INITIAL FROM STRING

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.

PG
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 224 views
  • 2 likes
  • 5 in conversation