BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
apolitical
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
rogerjdeangelis
Barite | Level 11
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

6 REPLIES 6
rogerjdeangelis
Barite | Level 11
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
Obsidian | Level 7
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
Super User

@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
Obsidian | Level 7

thanks. that in hindsight should have been obvious..

Reeza
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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