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

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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