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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.