topic Collapse Transactional Data by Customer and Preserve Order of Transaction Type in SAS Programming

Collapse Transactional Data by Customer and Preserve Order of Transaction Type

P5C768 — Fri, 06 Apr 2018 18:06:40 GMT

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!

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

ballardw — Fri, 06 Apr 2018 18:49:26 GMT

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.

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

P5C768 — Mon, 16 Apr 2018 15:37:14 GMT

Another great solution from @ballardw! Thank you!