Combine the Amount based on date

Jayaditya — Tue, 14 Sep 2021 09:28:51 GMT

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

Re: Combine the Amount based on date

Kurt_Bremser — Tue, 14 Sep 2021 10:32:09 GMT

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;

topic Combine the Amount based on date in SAS Programming

Combine the Amount based on date

Re: Combine the Amount based on date