DATA Step, Macro, Functions and more

Capturing values of a variable while collapsing by another variable

Accepted Solution Solved
Reply
Contributor
Posts: 66
Accepted Solution

Capturing values of a variable while collapsing by another variable

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.


Accepted Solutions
Solution
‎03-09-2017 03:32 PM
Valued Guide
Posts: 505

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

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

View solution in original post


All Replies
Solution
‎03-09-2017 03:32 PM
Valued Guide
Posts: 505

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

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

Contributor
Posts: 66

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

thank you. that works!
Contributor
Posts: 66

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

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
Super User
Posts: 10,516

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


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.

Contributor
Posts: 66

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

thanks. that in hindsight should have been obvious..

Super User
Posts: 17,868

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


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.

☑ This topic is SOLVED.

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

Discussion stats
  • 6 replies
  • 140 views
  • 0 likes
  • 4 in conversation