How do I transpose a data set that has multiple values for an ID variable?

[Hello_there]

I have this data set that I want to transform so that it's one row per ID. The problem is that there are multiple values for the an ID variable.

Here is the data set in question:

data have1;
infile datalines dsd dlm=",";
	input id $ category $ val $;
datalines;
001, color, blue
001, color, red
002, color, orange
003, color, red
003, color, orange,
003, color, blue
004, color, aqua
005, color, purple
; 
run;

Ideally, I would want it so it looks like:

id      category val1       val2         val3

001  color       blue       red

002  color      orange

003  color      red          orange     blue

004  color     aqua

005  color     purple

Thanks

[Tom · Accepted solution]

PROC TRANSPOSE can handle that example very easily.  Perhaps you over simplified your example?

data have1;
	input id $ category $ val $;
datalines;
001  color  blue
001  color  red
002  color  orange
003  color  red
003  color  orange 
003  color  blue
004  color  aqua
005  color  purple
; 

proc transpose data=have1 out=want1(drop=_name_) prefix=VAL ;
  by id category ;
  var val;
run;

Result

Obs    id     category    VAL1      VAL2      VAL3

 1     001     color      blue      red
 2     002     color      orange
 3     003     color      red       orange    blue
 4     004     color      aqua
 5     005     color      purple

[Hello_there]

Thanks!

[ballardw]

For your given example:

proc transpose data=have1 out=trans (drop=_name_)
  prefix=val;
   by id category;
   var val;
run;

Transpose uses the BY variables to create groupings.

The default behavior is to use the Prefix (defaults to col) as the name of the transposed variables with an increment for number of values in the by group.

If you were concerned with the ID statement that is for a variable used to NAME output variables and generally doesn't allow multiple values per By group.

[Hello_there]

Thanks for your reply!