Wednesday, 6 March 2013

Concatenation of two data fields in SAS EG

I often need to concatenate two fields, let's call them name_1 and name_2, into one field - Ful_name. I'm not entirely sure whether one or both of these fields has leading or trailing spaces.

My first attempt:

Strip(Name_1) || " " || Strip(Name_2)

works fine. I tested this code today and 7 million rows processed in 0.2 seconds.

My second attempt uses the CATX function. This takes the format:

CATX(delimiter, first_string, second_string, third_string etc)

So I used, CATX(' ', Name_1, Name_2)

CATX removes leading and trailing blanks from the specified strings and then concatenate these results using the specified delimiter - here a space character.

This version processed the 7 million rows in 0.1 seconds, a 50% improvement.

SAS built in functions will almost always work more efficiently than user designed versions, so it's worth studying the SAS functions!

There's documentation about CATX here.



6 comments:

  1. Hey Jeremy,
    I was wondering if you know of a way to concatenate two fields that both contain dates (e.g. START_DATE and END_DATE) in a way that retains their format as a dates once they are combined? I simply want to end up with something that looks like "08/26/2013 - 12/14/2013" . Thanks

    ReplyDelete
  2. Concatenating two date fields is slightly trickier.

    Using a table with one row, FirstDate LastDate
    01JAN2012 31DEC2012

    CATX(' - ',t1.FirstDate,t1.LastDate) is syntactically correct but will show 18993 - 19358.

    This is because SAS is displaying the two dates in the standard internal storage format.

    There are several different ways to force SAS to display "proper" dates. The PUTN statement works very well.

    CATX(' - ',putn(t1.FirstDate,'DDMMYY10.'),putn(t1.LastDate,'DDMMYY10.'))

    The result is: 01/01/2012 - 31/12/2012

    ReplyDelete
  3. Hi Jeremy,
    Can check with you? How do I combine two columns - Column A (Year) and Column B (Months) into one column in SAS Enterprise?

    ReplyDelete