topic Re: Capturing values of a variable while collapsing by another variable in SAS Programming

Capturing values of a variable while collapsing by another variable

apolitical — Thu, 09 Mar 2017 17:41:34 GMT

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.

Re: Capturing values of a variable while collapsing by another variable

rogerjdeangelis — Thu, 09 Mar 2017 17:56:22 GMT

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

Re: Capturing values of a variable while collapsing by another variable

Reeza — Thu, 09 Mar 2017 17:59:05 GMT

@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.

Re: Capturing values of a variable while collapsing by another variable

apolitical — Thu, 09 Mar 2017 20:33:02 GMT

thank you. that works!

Re: Capturing values of a variable while collapsing by another variable

apolitical — Fri, 10 Mar 2017 15:20:45 GMT

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

Re: Capturing values of a variable while collapsing by another variable

ballardw — Fri, 10 Mar 2017 23:25:59 GMT

@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.

Re: Capturing values of a variable while collapsing by another variable

apolitical — Fri, 17 Mar 2017 21:19:04 GMT

thanks. that in hindsight should have been obvious..