BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
camfarrell25
Quartz | Level 8

Hello,

I am trying to use the scan function to distinguish between first and last name, which are separated by a longer space. For example:

 

HOGUE STAUBIN      BENOIT G

 

The following code

BOR1_LASTNAME = trim(scan(BOR1_FULL_NAME_COR,1,"   "));

 

Breaks it down by:

HOGUE

 

When the result I would want is: HOGUE STAUBIN

 

Note that there's some instances where the last name is composed (two names) and sometimes single, but the first and last name(s) are always separated by a longer space.

 

Any insights?

1 ACCEPTED SOLUTION

Accepted Solutions
error_prone
Barite | Level 11

Can you post more data as text? I won't open ms office files.

 

The following regular expression can help solving the problem:

data have;
    text1 = "HOGUE STAUBIN      BENOIT G";
    rx = prxparse('/(\w+ \w*) +.*/');
    
    if prxmatch(rx, trim(text1)) then do;
    	last_name = prxposn(rx, 1, text1);
    end;
run;

View solution in original post

8 REPLIES 8
Reeza
Super User

Look into the modifiers (m) for the SCAN() function.

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=lefunctionsref&docsetTarg...

 


@camfarrell25 wrote:

Hello,

I am trying to use the scan function to distinguish between first and last name, which are separated by a longer space. For example:

 

HOGUE STAUBIN      BENOIT G

 

The following code

BOR1_LASTNAME = trim(scan(BOR1_FULL_NAME_COR,1,"   "));

 

Breaks it down by:

HOGUE

 

When the result I would want is: HOGUE STAUBIN

 

Note that there's some instances where the last name is composed (two names) and sometimes single, but the first and last name(s) are always separated by a longer space.

 

Any insights?


 

Reeza
Super User
Any chance your delimiter is actually a tab? Can you use '09'x to see if it's a tab or verify in a text editor?
camfarrell25
Quartz | Level 8

I tried the following and no luck..

BOR1_LASTNAME = trim(scan(BOR1_FULL_NAME_COR,1,'09'x));
Reeza
Super User

Is this from a text file? Can you upload a small sample? Pasting as text may remove issues in the actual data unfortunately. 

 

 

camfarrell25
Quartz | Level 8

It's not pulled from a text file - see attached. Essentially the logic that would work is to be able to separate the first and last name in two if the space between them exceeds one, and then further breaking it down into first last name, and second last name based on the presence of a single space.

Is there a way?

Reeza
Super User

@camfarrell25 wrote:

It's not pulled from a text file - see attached. Essentially the logic that would work is to be able to separate the first and last name in two if the space between them exceeds one, and then further breaking it down into first last name, and second last name based on the presence of a single space.

Is there a way?


I'm assuming you're actually asking how, rather than if it's possible, otherwise the answer is yes. 

Use FINDW to find a string of two spaces, then use SUBSTR at that location. 

I also used STRIP to remove any leading spaces from the second portion. 

 

data have;
    text1 = "HOGUE STAUBIN      BENOIT G";
    x=findw(text1, "  ");
    p1 = substr(text1, 1, x);
    p2 = strip(substr(text1, x+1));
run;
error_prone
Barite | Level 11

Can you post more data as text? I won't open ms office files.

 

The following regular expression can help solving the problem:

data have;
    text1 = "HOGUE STAUBIN      BENOIT G";
    rx = prxparse('/(\w+ \w*) +.*/');
    
    if prxmatch(rx, trim(text1)) then do;
    	last_name = prxposn(rx, 1, text1);
    end;
run;
camfarrell25
Quartz | Level 8

Hello,

I'm sorry - find attach text file.


CF

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 4416 views
  • 2 likes
  • 3 in conversation