Help using Base SAS procedures

Transpose the data

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

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

Thanks in advance!


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;

View solution in original post


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: 7,417

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=_Smiley Happy 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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 205 views
  • 6 likes
  • 4 in conversation