Combine the Amount based on date

[Jayaditya]

Hi,

data work.testl;
infile datalines ;
input Policy_number:$8. Test_Rk:8. Version:8. Amt:8. from_dt:date. to_dt:date.;
datalines;
1 10 1 300 '01Jan2006'd  '01Jan2034'd
1 10 1 700 '01Jan2035’d  '01Jun2035'd
1 10 1 300 '01Jan2034'd  '01Jan2035'd

I have the above example. In which I want the output to be the following:

1 10 1 300 '01JAN2006'd  '01JUN2035'd

1 10 1 700 '01JAN2035'd  '01JUN2035'd

ie. if the amount is the same for the subsequent  from_dt for the same

Policy_number,Test_Rk, Version then the periods must be combined to one bigger period(from_dt = min(from_dt) and to_dt = Max(to_dt)     -

  1 10 1 300 '01JAN2006'd '01JUN2035'd

if the amount is different then don't combine -  1 10 1 700 '01JAN2035'd '01JUN2035'd

How can I achieve this?

Many Thanks,

Jay

[Kurt_Bremser · Accepted solution]

See this:

proc sort data=testl;
by policy_number from_dt;
run;

data want;
do until (last.amt);
  set testl;
  by policy_number amt notsorted;
  if first.amt then f_dt = from_dt;
end;
from_dt = f_dt;
drop f_dt;
run;