topic Denormalizing data in SAS Programming

Denormalizing data

HB — Thu, 02 May 2019 22:26:04 GMT

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?

Re: Denormalizing data

Jagadishkatam — Thu, 02 May 2019 22:29:01 GMT



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;

Re: Denormalizing data

HB — Thu, 02 May 2019 22:33:50 GMT

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.

Re: Denormalizing data

ScottBass — Thu, 02 May 2019 22:42:09 GMT

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