BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
csetzkorn
Lapis Lazuli | Level 10

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.

1 ACCEPTED SOLUTION

Accepted Solutions
FredrikE
Rhodochrosite | Level 12

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

View solution in original post

6 REPLIES 6
PeterClemmensen
Tourmaline | Level 20

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
Lapis Lazuli | Level 10
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
Rhodochrosite | Level 12

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
Lapis Lazuli | Level 10
Yeah thanks - thought so ...
Ksharp
Super User
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
Opal | Level 21

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

 

 

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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