Why is it that the simplest requirement flummoxs me at times? Please can someone help?

Reply
New Contributor
Posts: 3

Why is it that the simplest requirement flummoxs me at times? Please can someone help?

I'm using SAS EG currently but have been using SAS as part of my role for a number of years but still get caught out by what seems to be the simplest thing!

I have a variable called name which contains title/forename/surname and I need to separate them. The problem I have is that the surname in this data can be comprised of 1,2 or 3+ words separated by blanks e.g. Mr David Le Smythe Forbes or Mrs Karen Le Mure.

I've been playing with scan/substr/index but nothing quite works e.g.


SCAN(t1.Name,3,' ') gives me the 1st word of the surname e.g. Le so 1st thought is, is it possible to make it read past the second space delimiter to the end of the variable?


SUBSTR(t1.Name,INDEX(t1.Name," ")) gives me the name from the forename onwards e.g. David Le Smythe Forbes or Karen Le Mure so 2nd thought is, can I make this read from the second blank on rather than the first, surely this is possible?

I just can't quite fathom it so any help would be gratefully received.

Super User
Posts: 5,256

Re: Why is it that the simplest SAS requirement flummoxs me at times? Please can someone help?

Do you always have a title, and just one forename?

You are on the right track, and there is nothing ready made, I believe.

You can use call scan(), which gives you the starting position for the nth word, which you can use later in a substr to get your surname.

The drawback is that this can only be called in a data step, not in the standard EG tasks.

Data never sleeps
New Contributor
Posts: 3

Re: Why is it that the simplest SAS requirement flummoxs me at times? Please can someone help?

Yes scanning through the data it looks like all have a title and forename.

I'll do some reading on call scan because I can do a data step in the program task in EG.

Thanks

Respected Advisor
Posts: 3,777

Re: Why is it that the simplest requirement flummoxs me at times? Please can someone help?

If you just need everything from the beginning of the third word to the end use call scan to find the position of the third word and substr from there.

data _null_;
  
input name $30.;
  
title = scan(name,1,' ');
   forename = scan(name,2,' ');
   call scan(name,3,p,l,' ');
   surname = substr(name,p);
  
put (_char_)(30.) (_numeric_)(=);
   cards;
Mr David Le Smythe Forbes
Mrs Karen Le Mure
;;;;
   run;
Super User
Posts: 5,081

Re: Why is it that the simplest requirement flummoxs me at times? Please can someone help?

You do have to realize that there is no one automated solution.  You will likely encounter names like Mrs. Mary Ellen Smith.  The surname isn't the only portion that might arrive in two pieces.

New Contributor
Posts: 3

Re: Why is it that the simplest requirement flummoxs me at times? Please can someone help?

Thanks all, the call scan function worked but yes Astounding I do realise it isn't infallible but it was the best solution I could find (other than giving the original developer a clip round the ear) Smiley Wink

Ask a Question
Discussion stats
  • 5 replies
  • 240 views
  • 6 likes
  • 4 in conversation