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.
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
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?
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
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.