Collapse Transactional Data by Customer and Preserve Order of Transaction Type

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 86
Accepted Solution

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
Monday
Super User
Posts: 12,676

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.

 

View solution in original post


All Replies
Solution
Monday
Super User
Posts: 12,676

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: 86

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

Another great solution from @ballardw!  Thank you!

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 70 views
  • 2 likes
  • 2 in conversation