DATA Step, Macro, Functions and more

transpose dataset by category

Accepted Solution Solved
Reply
Regular Contributor
Posts: 210
Accepted Solution

transpose dataset by category

[ Edited ]

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.


Accepted Solutions
Solution
‎01-26-2018 10:23 AM
PROC Star
Posts: 392

Re: transpose dataset by category

Posted in reply to csetzkorn

DO in three steps?!

 

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

 

//Fredrik

View solution in original post


All Replies
PROC Star
Posts: 1,400

Re: transpose dataset by category

Posted in reply to csetzkorn

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?

Regular Contributor
Posts: 210

Re: transpose dataset by category

I want to use this down stream for some clustering. I guess my 'solution' works. i can just join all the datasets later on ...
Solution
‎01-26-2018 10:23 AM
PROC Star
Posts: 392

Re: transpose dataset by category

Posted in reply to csetzkorn

DO in three steps?!

 

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

 

//Fredrik

Regular Contributor
Posts: 210

Re: transpose dataset by category

Yeah thanks - thought so ...
Super User
Posts: 10,848

Re: transpose dataset by category

Posted in reply to csetzkorn
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;


Super User
Posts: 8,214

Re: transpose dataset by category

Posted in reply to csetzkorn

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

 

 

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 144 views
  • 4 likes
  • 5 in conversation