Return to Archive

SAS Tip of the Month
September 2005

There are many ways within SAS to get the last word in a character variable but my old favorite is still to start from the beginning and scan across the variable finding the position of the last word and then grabbing that word and saving it in a variable. The following example illustrates the method:

The SAS Code

    data _null_;
        infile cards;
        input name $ 1-15;
        length surname $15;
        i=1;
        do while(scan(name,i) ne "");
            i+1;
        end;
        surname=scan(name,i-1);
        put @1 name= @25 surname=;
    cards;
    Alfred Domett
    William Fox
    Edward Stafford
    William Fox
    Henry Sewell
    ;
    run;

The Output to the Log

    name=Alfred Domett      surname=Domett
    name=William Fox        surname=Fox
    name=Edward Stafford    surname=Stafford
    name=William Fox        surname=Fox
    name=Henry Sewell       surname=Sewell

There are other methods, including ones that start at the end, but this one is something I carry in my toolbox.

I get asked from time to time if there is a similar routine for Microsoft Excel. There is no inbuilt function for this but here is some VBA code that I use to create a function that when called will do the job:

    Function LastWord(String_Text As String)
    Dim HaveGotIt As String
    i = 1
    Do Until HaveGotIt Like (" *")
        HaveGotIt = Right(String_Text, i)
        i = i + 1
    Loop
    LastWord = Trim(HaveGotIt)
    End Function

I do hope you find this tip useful.