Transpose the data

[LoveSAS]

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

Thanks in advance!

[DBailey · Accepted solution]

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;

[Data_Detective_23219]

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;

[art297]

: 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;

[LoveSAS]

Thank you so much guys!