Denormalizing data

[HB]

I have data in one format and need to get it to another (nonnormalized) format for manipulation/display purposes.

data have;
input mycode mytype $1.;
datalines;
111 a
111 b
111 c
222 a
222 b
222 d
222 e
222 f
222 g
333 a
333 q
444 s
555 s
555 e
555 t
666 a
666 b
;
run;

The desired yucky format is 

111 a b c
222 a b d e f g
333 a q
444 s
555 s e t
666 a b

I know this can be done, but I don't know how to do it. 

Essentially I think I want to sort by mycode and then do a loop that is:

      for each value of mycode until the last.mycode in each group make mynewtype = mynewtype concatenate mytype on the end

but obviously I don't know how to translate that into working code.

I don't care whether 

222 a b d e f g

comes out as 7 columns or 2 columns with "a b d e f g" as the value in the second column. 

Could somebody please point me in the correct direction?

[Jagadishkatam · Accepted solution]

data want;
length newx $20.;
set have;
by mycode;
retain newx;
if first.mycode then newx='';
if mytype ne '' then newx=catx(' ',newx,mytype);
if last.mycode;
drop mytype;
run;

[HB]

That results in 

newx	mycode
a b c	111
a b d e f g	222
a q	333
s	444
s e t	555
a b	666

which will work just fine.

World record response time!!

Thank you. 

[ScottBass]

I don't care whether 

222 a b d e f g

comes out as 7 columns or 2 columns with "a b d e f g" as the value in the second column. 

Another approach is to use PROC TRANSPOSE, where you can have "the best of both worlds":

data have;
input mycode mytype $1.;
datalines;
111 a
111 b
111 c
222 a
222 b
222 d
222 e
222 f
222 g
333 a
333 q
444 s
555 s
555 e
555 t
666 a
666 b
;
run;

proc transpose data=have out=want (drop=_name_);
   by mycode;
   var mytype;
run;

data vwant / view=vwant;
   set want;
   length mytype $100;
   mytype=catx(" ",of col:);
   keep mycode mytype;
run;

HTH...