transpose dataset by category

[csetzkorn]

I have a dataset in this form:

Category Month Measure1 Measure2
C1 1 9 7
C1 2 3 3
C1 3 7 3
C1 4 8 3
C1 5 5 6
C1 6 1 8
C1 7 4 1
C1 8 10 3
C1 9 1 4
C1 10 1 5
C1 11 8 3
C1 12 1 4
C2 1 3 4
C2 2 1 9
C2 3 2 7
C2 4 3 9
C2 5 1 2
C2 6 9 1
C2 7 9 8
C2 8 7 1
C2 9 5 4
C2 10 3 10
C2 11 4 6
C2 12 4 10

I would like to transform it into this format:

Category Measure1_1 Measure1_2 Measure1_3 Measure1_4 Measure1_5 Measure1_6 Measure1_7 Measure1_8 Measure1_9 Measure1_10 Measure1_11 Measure1_12 Measure2_1 Measure2_2 Measure2_3 Measure2_4 Measure2_5 Measure2_6 Measure2_7 Measure2_8 Measure2_9 Measure2_10 Measure2_11 Measure2_12
C1
C2

So there are 2 rows for each category and a column for each measure and month:

Category, Measure1_1 ... Measure1_12, Measure2_1 ... Measure2_12

Any help would be very much appreciated. Thanks.

PS:

This:

PROC TRANSPOSE data=new out=want (drop=_:) prefix=var1_;
 by Category;
 var Measure1;
 id Month;
run;

comes close.

[PeterClemmensen]

Since you are close yourself, first I will ask the question Why do you want to do this? Seems to make your data structure more complex?

[csetzkorn]

I want to use this down stream for some clustering. I guess my 'solution' works. i can just join all the datasets later on ...

[FredrikE · Accepted solution]

DO in three steps?!

PROC TRANSPOSE data=new out=want (drop=_:) prefix=var1_;
by Category;
var Measure1;
id Month;
run;
PROC TRANSPOSE data=new out=want2 (drop=_:) prefix=var2_;
by Category;
var Measure2;
id Month;
run;
data want3;
merge want want2;
by category;
run;

//Fredrik

[csetzkorn]

Yeah thanks - thought so ...

[Ksharp]

data have;
input Category $ Month Measure1 Measure2;
cards;
C1 1 9 7
C1 2 3 3
C1 3 7 3
C1 4 8 3
C1 5 5 6
C1 6 1 8
C1 7 4 1
C1 8 10 3
C1 9 1 4
C1 10 1 5
C1 11 8 3
C1 12 1 4
C2 1 3 4
C2 2 1 9
C2 3 2 7
C2 4 3 9
C2 5 1 2
C2 6 9 1
C2 7 9 8
C2 8 7 1
C2 9 5 4
C2 10 3 10
C2 11 4 6
C2 12 4 10
;
run;
proc sql noprint;
select max(n) into : n from
(select count(*) as n  from have group by category);
quit;

proc summary data=have ;
by category;
output out=want idgroup(out[&n] (Measure1 Measure2)=);
quit;

[art297]

You already have a solution, and a couple of alternative solutions, but thought you might be interested in knowing about a method that I think is both easier and faster. A group of us wrote a macro to accomplish such tasks. You can download it from: http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset

Once you download and run that macro, the following call would accomplish the task at hand:

%transpose(data=new, out=want, by=Category, var=Measure1 Measure2,
 id=month, delimiter=_)

Art, CEO, AnalystFinder.com