## transpose

Solved
Super Contributor
Posts: 285

# transpose

[ Edited ]

Hello,

How to get the desired output without using TRANSPOSE (entirely in data step)?

``````data a;
input x \$ y \$ z;
cards;
t1 a 1
t2 a 1
t3 a 1
t1 b 2
t2 b 2
t3 b 2
;
run;

proc sort data = a;
by x;
run;

proc transpose data = a out=b (drop = _name_);
by x;
var y;
run;

data want;
set b;
y=cats(col1,col2);
drop col1 col2;
run;``````

Accepted Solutions
Solution
‎06-29-2017 05:09 PM
Super User
Posts: 13,507

## Re: transpose

Here's an extended version of my previous to encorporate a sum.

```data want;
set a;
by x;
length result \$ 100;
retain result zsum;
if first.x then do;
result=y;
zsum=z;
end;
else do;
result= cats(result,y);
zsum = sum(zsum,z);
end;
if last.x;
keep x result zsum;
run;```

As a suggestion for future questions, it would be a better example if the row results are not essentially the same as your result may not demonstrate the actualy desired result well.

```data a;
input x \$ y \$ z;
cards;
t1 a 1
t2 a 2
t3 c 4
t1 b 5
t2 c 6
t3 b 7
;
run;```

for instance will get different results when using

proc sort data = a;

by x y;

run;

or

proc sort data = a;

by x ;

run;

which may be important.

All Replies
Super User
Posts: 13,507

## Re: transpose

Is there any reason you included your variable Z in data set A since it has no bearing or presence in the end result?

For your limited example this replicates the result.

```ata a;
input x \$ y \$ z;
cards;
t1 a 1
t2 a 1
t3 a 1
t1 b 2
t2 b 2
t3 b 2
;
run;

proc sort data = a;
by x y;
run;

data want;
set a;
by x;
length result \$ 100;
retain result;
if first.x then result=y;
else result= cats(result,y);
if last.x;
keep x result;
run;```
Super Contributor
Posts: 285

## Re: transpose

[ Edited ]

@ballardw Thank you. I also meant to add the values of  z  for a and b of y for each x creating third column.

x     result    z

t1    ab        3

t2    ab        3

t3    ab        3

PROC Star
Posts: 8,163

## Re: transpose

Easy enough to add the 3rd column:

```data want (drop=_:);
set a (rename=(z=_z));
by x;
y=ifc(last.x,catt(lag(y),y),'');
z=ifc(last.x,catt(lag(_z),_z),'');
if last.x then output;
run;
```

Art, CEO, AnalystFinder.com

Super Contributor
Posts: 285

## Re: transpose

@art297 I meant to add (1+2) = 3, not concatenate.

Contributor
Posts: 23

## Re: transpose

Then just use the sum function instead of the catt function

Solution
‎06-29-2017 05:09 PM
Super User
Posts: 13,507

## Re: transpose

Here's an extended version of my previous to encorporate a sum.

```data want;
set a;
by x;
length result \$ 100;
retain result zsum;
if first.x then do;
result=y;
zsum=z;
end;
else do;
result= cats(result,y);
zsum = sum(zsum,z);
end;
if last.x;
keep x result zsum;
run;```

As a suggestion for future questions, it would be a better example if the row results are not essentially the same as your result may not demonstrate the actualy desired result well.

```data a;
input x \$ y \$ z;
cards;
t1 a 1
t2 a 2
t3 c 4
t1 b 5
t2 c 6
t3 b 7
;
run;```

for instance will get different results when using

proc sort data = a;

by x y;

run;

or

proc sort data = a;

by x ;

run;

which may be important.

PROC Star
Posts: 8,163

## Re: transpose

Or, if you want an approach that uses the lag function:

```data want;
set a (drop=z);
by x;
y=ifc(last.x,catt(lag(y),y),'');
if last.x then output;
run;
```

Art, CEO, AnalystFinder.com

Super Contributor
Posts: 285

## Re: transpose

[ Edited ]

@art297 Thank you. This answer  also serves my purpose, but can accept only one soution.

☑ This topic is solved.