Spreading Multiple (Monthly) Obserations into a Single Observation

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

Spreading Multiple (Monthly) Obserations into a Single Observation
~ Monthly Quantitative Fields are Summed ~
Bruce Ratner, Ph.D.

title1' Multiple (Monthly Payment) Observations ';

data multi_obs;
input @1 ID $2. @4 month_paid mmddyy10. @16 amount_paid dollar5.;
format month_paid mmddyys10.;
format amount_paid dollar5.;
cards;
BR 1/9/2000     $12
BR 2/5/2007     $67
BR 3/12/2007   $34
BR 4/6/2007     $56
BR 5/11/2007   $67
BR 6/4/2007     $78
BR 7/8/2007     $12
BR 8/13/2007 $97
BR 9/30/2007 $84
BR 10/1/2007   $86
BR 11/20/2007 $68
BR 12/7/2007   $88
AR 6/23/2007 $23
AR 7/21/2007   $45
AR 8/11/2007   $55
AR 9/22/2007   $87
AR 10/25/2007 $90
AR 12/18/2007 $45
AR 5/5/2008    $45
CC 5/3/2006    $10
CC 6/2/2006     $11
CC 8/1/2006     $12
CC 9/12/2006   $13
DD 9/23/2010   $199
;
run;

proc print;
run;

data multi_obs;
set multi_obs;
format amount_paid 10.2;
mon = month(month_paid);
year = year(month_paid);
run;

proc sort;by ID year mon;
run;

%let dsn = multi_obs;
data _NULL_;
set &dsn end=EOF;
retain maxyr 0000 minyr 9999;
maxyr = max(maxyr,year);
minyr = min(minyr,year);
if EOF then do;
     rangemon = 12*(sum(maxyr,-minyr) + 1);
     call symput('allmon',trim(left(put(rangemon,6.))));
     call symput('first',trim(left(put(minyr,6.))));
end;

data multi_obs_spread ;
retain ID begyr mon1-mon&allmon ;
set &dsn;
by ID;
array mons (&allmon) mon1-mon&allmon;
if first.ID then do;
     do i= 1 to &allmon;
     mons(i) = .;
end;
     begyr = year;
end;
diff = year - &first;
calcmon = diff*12 + mon;
mons(calcmon) = amount_paid;
if last.ID then do;
     output;
end;
keep ID begyr mon1-mon&allmon;
run;

proc print data=multi_obs_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.