Capturing values of a variable while collapsing by another variable

[apolitical]

Say we have a data like this:

transaction      step           action

1                      1                buy

1                      2                sell

1                      3                buy

2                     1                 buy

2                     2                 loan

2                     3                 sell

I want to collapse the data  into something like this:

transaction         action_sequence

1                        buy/sell/buy

2                        buy/loan/sell

and waht if i only want to keep the unique values, like this

transaction         actions

1                         buy/sell

2                         buy/loan/sell

Can this be done with proc sql? Thanks.

[rogerjdeangelis · Accepted solution]

data have;
input transaction step action $;
cards4;
1 1 buy
1 2 sell
1 3 buy
2 1 buy
2 2 loan
2 3 sell
;;;;
run;quit;

proc sort data=have out=havunq nodupkey;
by transaction action;
run;quit;

data want;
 length rol $200;
 retain rol ' ';
 set havunq;
 by transaction;
 rol=catx('/',rol,action);
 if last.transaction then do;
   output;
   rol=' ';
 end;
 keep transaction rol;
run;quit;



transaction   actions
1              buy/sell
2              buy/loan/sell

[apolitical]

thank you. that works!

[apolitical]

Oh and also, what if I want to record all actions in their sequence and not eliminate duplicates? for something like this:

transaction action_sequence
1 buy/sell/buy
2 buy/loan/sell

[ballardw]

---

@apolitical wrote:
Oh and also, what if I want to record all actions in their sequence and not eliminate duplicates? for something like this:

transaction action_sequence
1 buy/sell/buy
2 buy/loan/sell

---

Use @rogerjdeangelis solution but remove the NODUPKEY from the SORT should do it.

[apolitical]

thanks. that in hindsight should have been obvious..

[Reeza]

---

@apolitical wrote:
 

Can this be done with proc sql? Thanks.

---

This is not easy in PROC SQL, at least the SAS flavour. You essentially would transpose it and then collapse the values or use a recursive join of some type but know how many are required would make it a little more difficult. Oracle SQL or T-SQL have WINDOW or PARTITION syntax that make it easier.

A data step to collapse rows is much more efficient and much easier to code.