topic Re: Scanning for multiple spaces in SAS Programming

Scanning for multiple spaces

camfarrell25 — Wed, 22 Aug 2018 15:41:58 GMT

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?

Re: Scanning for multiple spaces

Reeza — Wed, 22 Aug 2018 15:43:40 GMT

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

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=lefunctionsref&docsetTarget=p0jshdjy2z9zdzn1h7k90u99lyq6.htm&locale=en#n1b0x6tlw5ztlzn1j6bclhsvc8ia

@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?

Re: Scanning for multiple spaces

Reeza — Wed, 22 Aug 2018 15:48:04 GMT

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?

Re: Scanning for multiple spaces

camfarrell25 — Wed, 22 Aug 2018 15:49:51 GMT

I tried the following and no luck..

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

Re: Scanning for multiple spaces

Reeza — Wed, 22 Aug 2018 15:50:47 GMT

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

Re: Scanning for multiple spaces

camfarrell25 — Wed, 22 Aug 2018 15:54:50 GMT

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?

Re: Scanning for multiple spaces

Reeza — Wed, 22 Aug 2018 16:24:59 GMT

@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;

Re: Scanning for multiple spaces

error_prone — Wed, 22 Aug 2018 19:10:22 GMT

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;

Re: Scanning for multiple spaces

camfarrell25 — Wed, 22 Aug 2018 19:37:00 GMT

Hello,

I'm sorry - find attach text file.