Rearranging the order of the text in a character string in SAS?

Question

I have a data set with a character variable called "name". It contains the full name of a person like this: "firstname middlename lastname".

I want to have the data rearranged so that is becomes: "lastname, firstname middlename".

I'm not that hardcore in SAS functions, but I have used some of the few I know.

(My code can be seen below).

In the first try (test2) I don't get the result I want - I get: "lastName , firstName middleName" and not "lastName, firstName middleName" - my problem is the comma.

So I thought that I would solve my problem by making af new last name variable containing the comma at the end (in test2_new). But I don't get what I want? SAS put three dots at the end, and not a comma?

I hope a person with more SAS skills than me, can answer my question??

Kind Regards Maria enter image description here

score 2 · Answer 1 · answered Jun 20 '14 at 13:25

2

data have ;
  input @1 text & $64. ;
datalines ;
Susan Smith
David A Jameson
Bruce Thomas Forsyth
;
run ;

data want ;
  set have ;
  lastname = scan(text,-1,' ') ;
  firstnames = substr(text,1,length(text)-length(lastname)) ;
  newname = catx(', ',lastname,firstnames) ;
run ;

Which gives

text                    lastname    firstnames      newname

Susan Smith             Smith       Susan           Smith, Susan
David A Jameson         Jameson     David A         Jameson, David A
Bruce Thomas Forsyth    Forsyth     Bruce Thomas    Forsyth, Bruce Thomas

answered Jun 20 '14 at 13:25

Chris J

7,549
2
25
25

Thanks for the answer. But this method does not work when you have a middleName variable? I guess then the newname would be: Forsyth, Bruce, Thomas? – user1626092 Jun 25 '14 at 11:10
Based on your original post, you don't need an explicit middlename variable, simply to rearrange a single string in the format 'first [middle] last' to 'last, first [middle]'. This method works for any number of middle names, zero upwards. – Chris J Jun 25 '14 at 13:29

score 2 · Answer 2 · answered Jun 20 '14 at 14:53

PERL expressions are a useful tool here, particularly PRXCHANGE. The SAS Support website provides a good example of how to reverse first and last name, here's a slight modification of that code. I've only catered for people with either 2 or 3 names, but it should be fairly simple to expand this if necessary. My code is based on the HAVE dataset created in the answer from @Chris J.

data want;
set have;
if countw(text)=2 then text = prxchange('s/(\w+) (\w+)/$2, $1/', -1, text);
else if countw(text)=3 then text = prxchange('s/(\w+) (\w+) (\w+)/$3, $1 $2/', -1, text);
run;

Rearranging the order of the text in a character string in SAS?

2 Answers2