## Transpose the data

Solved
Occasional Contributor
Posts: 12

# Transpose the data

Hi,

I want to to transpose the data with grouping in categories and new names like this,

From...

ID A1 A2  B1  B2 C1 C2

1  10  20  25  10  8   30

2  20  30  35   8   10 25

To be ....

ID Categories   Var1      Var2

1        A            10         20

1        B            25         10

1        C             8          30

2        A             20        30

2        B             35         8

2        C             10        25

Accepted Solutions
Solution
‎02-04-2013 08:35 AM
Super Contributor
Posts: 578

## Re: Transpose the data

sort of a brute force method:

data have;

input ID A1 A2  B1  B2 C1 C2;

cards;

1  10  20  25  10  8   30

2  20  30  35   8   10 25

run;

data want (keep=id category var1 var2);

set have;

category='A';

var1=a1;

var2=a2;

output;

category='B';

var1=b1;

var2=b2;

output;

category='C';

var1=c1;

var2=c2;

output;

run;

All Replies
Solution
‎02-04-2013 08:35 AM
Super Contributor
Posts: 578

## Re: Transpose the data

sort of a brute force method:

data have;

input ID A1 A2  B1  B2 C1 C2;

cards;

1  10  20  25  10  8   30

2  20  30  35   8   10 25

run;

data want (keep=id category var1 var2);

set have;

category='A';

var1=a1;

var2=a2;

output;

category='B';

var1=b1;

var2=b2;

output;

category='C';

var1=c1;

var2=c2;

output;

run;

Occasional Contributor
Posts: 14

## Re: Transpose the data

Use proc transpose and in the results your variable named id in your dataset will be _NAME_ .  Then substring the first character so you're left with A B C and sort as you please.

proc transpose

by id;

var A1 A2  B1  B2 C1 C2;

PROC Star
Posts: 8,163

## Re: Transpose the data

: I think the following will provide what you want:

data have;

input ID A1 A2  B1  B2 C1 C2;

cards;

1  10  20  25  10  8   30

2  20  30  35   8   10 25

;

proc transpose data=have out=tall;

by id;

run;

data tall;

set tall;

category=compress(_name_,,'kf');

_name_=compress(_name_,,'kd');

run;

proc transpose data=tall out=want (drop=_ prefix=var;

by id category;

id _name_ ;

run;

Occasional Contributor
Posts: 12

## Re: Transpose the data

Thank you so much guys!

🔒 This topic is solved and locked.