DATA Step, Macro, Functions and more

Parse a long text field with varying length results to new variables

Reply
Contributor
Posts: 30

Parse a long text field with varying length results to new variables

I have a text file that is not delimited and my name field looks like this:

 

JOHN    DOE

MARYELIZABETH    SMITH

JACKIE    DOE

SOPHIE    DOE

 

The field is $50. as name AND there are four spaces in between the firstname and the lastname

 

How do I parse it to give me FIRST NAME AND LAST NAME

 

Thank you in advance

Super User
Posts: 2,075

Re: Parse a long text field with varying length results to new variables

did you try scan function?

data output;

set input;

FIRST_NAME=scan(name, 1);

 LAST_NAME=scan(name,2);

run;

 

Contributor
Posts: 30

Re: Parse a long text field with varying length results to new variables

Posted in reply to novinosrin

thanks I did try what you said and also this and both did not work

first_nm=scan(r_name,1,' '); last_nm=scan(r_name,2,' ');

 

Contributor
Posts: 30

Re: Parse a long text field with varying length results to new variables

I ended up doing this - thank you Smiley Happy
format lname $15.;
format fname $15.;
lname=scan(r_name,1,' ');
fname=left(scan(r_name,2,' '))
Super User
Posts: 2,075

Re: Parse a long text field with varying length results to new variables

Ok Good Smiley Happy

Super Contributor
Posts: 320

Re: Parse a long text field with varying length results to new variables

If both first and last name are guaranteed not to have any spaces, you can just use SCAN.

 

If you specify 4 spaces because you want to treat single spaces as non-delimiters, then you have a few options.  The easiest is to use DLMSTR as '    ', I think.  

 

data want;
length first last $50;
infile datalines dlmstr='    ';
input
first $ last $;
;;;;
datalines;
JOHN    DOE
MARY ELIZABETH    SMITH
JACKIE    DOE
SOPHIE    DOE
;;;;
run;

But if you have other fields and want to read them as fixed width, you could read into NAME, then FIND '   ' and then use SUBSTR to parse it, or a perl regular expression.

Ask a Question
Discussion stats
  • 5 replies
  • 140 views
  • 0 likes
  • 3 in conversation