## Collapse Transactional Data by Customer and Preserve Order of Transaction Type

Solved
Frequent Contributor
Posts: 89

# Collapse Transactional Data by Customer and Preserve Order of Transaction Type

Hello,

Given data of the following format, how could one collapse the transactional records to a single row (customer) and preserve the order of the transaction type?  I.e. the result using the code below should be a single row for customer A with transaction history of 'offline-online-offline':

``````data sample;
input cust \$ type \$;
datalines;
A offline
A offline
A offline
A offline
A offline
A online
A online
A online
A online
A online
A online
A offline
A offline
A offline
A offline
A offline
;
run;``````

Assume the data is already sorted by customer and in ascending order by a timestamp variable not shown here.  Customers could have a single interaction type or up to 9 different types, with the average number of customer interactions is approx. 50 per customer.  Any assistance is much appreciated.  Thanks!

Accepted Solutions
Solution
‎04-16-2018 11:34 AM
Super User
Posts: 13,517

## Re: Collapse Transactional Data by Customer and Preserve Order of Transaction Type

I think this may get you started:

```Data want;
set sample;
length longtext \$500; /*this number has to be long enough to hold thelongest expected string*/
retain longtext;
by cust notsorted type;
if first.cust then call missing(longtext);
if first.type then longtext=catx('-',longtext,type);
if last.cust;
run;```

Any not named variables would have the values from the last record for the cust value.

All Replies
Solution
‎04-16-2018 11:34 AM
Super User
Posts: 13,517

## Re: Collapse Transactional Data by Customer and Preserve Order of Transaction Type

I think this may get you started:

```Data want;
set sample;
length longtext \$500; /*this number has to be long enough to hold thelongest expected string*/
retain longtext;
by cust notsorted type;
if first.cust then call missing(longtext);
if first.type then longtext=catx('-',longtext,type);
if last.cust;
run;```

Any not named variables would have the values from the last record for the cust value.

Frequent Contributor
Posts: 89

## Re: Collapse Transactional Data by Customer and Preserve Order of Transaction Type

Another great solution from @ballardw!  Thank you!

☑ This topic is solved.