Collapsing Multiple Observations for an Individual into a Single Observation

Data defines the model by dint of genetic programming, producing the best decile table.

Collapsing Multiple Observations for an Individual into a Single Observation
Bruce Ratner, Ph.D.

Objective: Reshape data by collapsing multiple records for an individual into a single record.

Solution:

collaspe-single-obs

data orders;
input id y z $;
cards;
1 1 b
1 7 r
1 9 g
1 14 u
2 1 c
3 2 e
;
run;
proc print;title1' orders ';
run;

data _NULL_;
set orders end=last_obs;
by id;
retain max_count;
if first.id then count = .;
count + 1;
if last.id then max_count = max(max_count, count);
if last_obs then call symputx('max_count', max_count);
run;

%put &max_count;

data orders_spread (keep=id y1-y&max_count z1-z&max_count);
retain y1-y&max_count z1-z&max_count; array y_ (*) y1-y&max_count;
array z_ (*) $ z1-z&max_count;
set orders;
by id;
if first.id then do;
i=1;
do j= 1 to &max_count;
y_(j)= . ;
z_(j)= ' ' ;
end;
end;
y_(i)=y; z_(i)=z;
if last.id then output;
i+1;
run;

proc print data=orders_spread;
title' orders_spread ';
run;

For more information about this article, call Bruce Ratner at 516.791.3544 or 1 800 DM STAT-1; or e-mail at br@dmstat1.com.

Sign-up for a free GenIQ webcast: Click here.