## Capturing values of a variable while collapsing by another variable

Solved
Frequent Contributor
Posts: 94

# Capturing values of a variable while collapsing by another variable

Say we have a data like this:

transaction      step           action

1                      2                sell

2                     2                 loan

2                     3                 sell

I want to collapse the data  into something like this:

transaction         action_sequence

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

transaction         actions

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 2 sell
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

``````

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 2 sell
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

``````
Frequent Contributor
Posts: 94

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

thank you. that works!
Frequent Contributor
Posts: 94

## 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
Super User
Posts: 13,498

## 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

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

Frequent Contributor
Posts: 94

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

thanks. that in hindsight should have been obvious..

Super User
Posts: 23,663

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