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?
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;
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;
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.
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...
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.