04-10-2013 07:22 AM
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.
04-10-2013 07:38 AM
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.
04-10-2013 08:50 AM
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.
04-10-2013 08:53 AM
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.
04-10-2013 10:12 AM
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.
04-10-2013 11:23 AM
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)