Data manipulation based on values in base SAS

[NikosStratis]

Hello all,
I have a situation that I cannot solve, and I would appreciate your valuable input.
I have the following data:

Policy_No From_Date To_Date Amount Ind
10864457 08Nov2013 08May2014 29,93 Pos
10864457 08May2014 08Nov2014 28,43 Pos
10864457 08Nov2014 08May2015 28,43 Pos
10864457 08May2015 08Nov2015 29,91 Pos
10864457 08Nov2015 08May2016 26,91 Pos
10864457 05Jan2016 08May2016 -18,39 Neg
10988488 23Sep2013 23Sep2014 63,89 Pos
11295729 04Dec2015 04Jun2016 38,69 Pos
11295729 10Feb2016 19Feb2016 -1,94 Neg
11295729 19Feb2016 04Jun2016 -22,56 Neg

I need to merge the Neg values into the last Pos Value of each Policy (my key)
And replace the last To Date of the last Pos line with the min From Date of the Neg line(s).
My output shall look like this:
Policy_No From_Date To_Date Amount Ind
10864457 08Nov2013 08May2014 29,93
10864457 08May2014 08Nov2014 28,43
10864457 08Nov2014 08May2015 28,43
10864457 08May2015 08Nov2015 29,91
10864457 08Nov2015 05Jan2016 8,52
10988488 23Sep2013 23Sep2014 63,89
11295729 04Dec2015 10Feb2016 14,19

Comments:
For Policy 10864457 the LAST Pos Value was 26,91 and the Neg Value was -18,39. So the result is To_Date = 05Jan2016 (which is the From date of the Neg Value and the new Amount is (26,91 – 18,39) = 8,52
For Policy 10988488 nothing changes since there is no Neg Value

For Policy 11292729 the To_Date becomes 10Feb2016 (the Min From Date of the Neg Values and the amount is now (38,69 – 1,94 – 22,56) = 14,19

Any suggestions on how to go on this?
Thank you in advance!

[Kurt_Bremser]

Sort by descending date_from, accumulate while ind = 'Neg', and add at first 'Pos' observation. Then re-sort to original order.

data have;
input Policy_No $ From_Date :date9. To_Date :date9. Amount :commax. Ind $;
format from_date to_date date9.;
datalines;
10864457 08Nov2013 08May2014 29,93 Pos
10864457 08May2014 08Nov2014 28,43 Pos
10864457 08Nov2014 08May2015 28,43 Pos
10864457 08May2015 08Nov2015 29,91 Pos
10864457 08Nov2015 08May2016 26,91 Pos
10864457 05Jan2016 08May2016 -18,39 Neg
10988488 23Sep2013 23Sep2014 63,89 Pos
11295729 04Dec2015 04Jun2016 38,69 Pos
11295729 10Feb2016 19Feb2016 -1,94 Neg
11295729 19Feb2016 04Jun2016 -22,56 Neg
;

proc sort data=have;
by policy_no descending from_date;
run;

data want;
set have;
by policy_no;
if first.policy_no
then neg_amount = 0;
if ind = 'Pos'
then do;
  amount + neg_amount;
  output;
  neg_amount = 0;
end;
else neg_amount + amount;
drop neg_amount;
run;

proc sort data=want;
by policy_no from_date;
run;

proc print data=want noobs;
run;

Result:

Policy_
   No       From_Date      To_Date    Amount    Ind

10864457    08NOV2013    08MAY2014     29.93    Pos
10864457    08MAY2014    08NOV2014     28.43    Pos
10864457    08NOV2014    08MAY2015     28.43    Pos
10864457    08MAY2015    08NOV2015     29.91    Pos
10864457    08NOV2015    08MAY2016      8.52    Pos
10988488    23SEP2013    23SEP2014     63.89    Pos
11295729    04DEC2015    04JUN2016     14.19    Pos

Note how I presented example data in a data step with datalines. Please do so yourself in the future.

[NikosStratis]

Hello!

Thank you very much for your quick response!

The Amount is OK, but the From Date issue remains.

[Kurt_Bremser]

---

@NikosStratis wrote:

Hello!

Thank you very much for your quick response!

The Amount is OK, but the From Date issue remains.

---

I adapted my code to handle the date:

data have;
input Policy_No $ From_Date :date9. To_Date :date9. Amount :commax. Ind $;
format from_date to_date date9.;
datalines;
10864457 08Nov2013 08May2014 29,93 Pos
10864457 08May2014 08Nov2014 28,43 Pos
10864457 08Nov2014 08May2015 28,43 Pos
10864457 08May2015 08Nov2015 29,91 Pos
10864457 08Nov2015 08May2016 26,91 Pos
10864457 05Jan2016 08May2016 -18,39 Neg
10988488 23Sep2013 23Sep2014 63,89 Pos
11295729 04Dec2015 04Jun2016 38,69 Pos
11295729 10Feb2016 19Feb2016 -1,94 Neg
11295729 19Feb2016 04Jun2016 -22,56 Neg
;

proc sort data=have;
by policy_no descending from_date;
run;

data want;
set have;
by policy_no;
retain neg_amount neg_date;
if first.policy_no
then do;
  neg_amount = 0;
  neg_date = .;
end;
if ind = 'Pos'
then do;
  amount + neg_amount;
  if neg_date ne .
  then do;
    to_date = neg_date;
    neg_date = .;
  end;
  output;
  neg_amount = 0;
end;
else do;
  neg_amount + amount;
  neg_date = (ifn(neg_date = .,from_date,min(from_date,neg_date)));
end;
drop neg_amount neg_date;
run;

proc sort data=want;
by policy_no from_date;
run;

proc print data=want noobs;
run;

Result:

Policy_
   No       From_Date      To_Date    Amount    Ind

10864457    08NOV2013    08MAY2014     29.93    Pos
10864457    08MAY2014    08NOV2014     28.43    Pos
10864457    08NOV2014    08MAY2015     28.43    Pos
10864457    08MAY2015    08NOV2015     29.91    Pos
10864457    08NOV2015    05JAN2016      8.52    Pos
10988488    23SEP2013    23SEP2014     63.89    Pos
11295729    04DEC2015    10FEB2016     14.19    Pos

[NikosStratis]

Thanks again!

[Jagadishkatam · Accepted solution]

please  try below code

data have;
input Policy_No From_Date:date9. To_Date:date9. Amount Ind$;
format From_Date To_Date date9.;
cards;
10864457 08Nov2013 08May2014 29.93 Pos
10864457 08May2014 08Nov2014 28.43 Pos
10864457 08Nov2014 08May2015 28.43 Pos
10864457 08May2015 08Nov2015 29.91 Pos
10864457 08Nov2015 08May2016 26.91 Pos
10864457 05Jan2016 08May2016 -18.39 Neg
10988488 23Sep2013 23Sep2014 63.89 Pos
11295729 04Dec2015 04Jun2016 38.69 Pos
11295729 10Feb2016 19Feb2016 -1.94 Neg
11295729 19Feb2016 04Jun2016 -22.56 Neg
;

proc sql;
create table neg as select Policy_No, min(From_Date)  as From_Date2 format=date9., sum(amount) as amount2 from have where lowcase(ind)='neg' group by Policy_No;
quit;

data want;
merge have(in=a where=(ind='Pos')) neg(in=b);
by Policy_No;
if a and b and last.Policy_No then do;
To_Date=From_Date2;
amount=amount-abs(amount2);
end;
drop amount2 From_Date2;
run;

[NikosStratis]

This is great!
Thank you!!!

[novinosrin]

My share of fun too. Just a bit of gymastics to park in temp vars and a verbose

data have;
input Policy_No $ (From_Date To_Date)  (:date9.) Amount :comma10.2 Ind $;
format From_Date To_Date  date9.;
cards;
10864457 08Nov2013 08May2014 29,93 Pos
10864457 08May2014 08Nov2014 28,43 Pos
10864457 08Nov2014 08May2015 28,43 Pos
10864457 08May2015 08Nov2015 29,91 Pos
10864457 08Nov2015 08May2016 26,91 Pos
10864457 05Jan2016 08May2016 -18,39 Neg
10988488 23Sep2013 23Sep2014 63,89 Pos
11295729 04Dec2015 04Jun2016 38,69 Pos
11295729 10Feb2016 19Feb2016 -1,94 Neg
11295729 19Feb2016 04Jun2016 -22,56 Neg
;


data want;
 do _n_=1 by 1 until(last.Policy_No);
  set have;
  by Policy_No From_Date;
  if Ind='Pos' then do;
  _last_positive_amt=Amount;
  _last_positive_date=From_Date;
  end;
  else if Ind='Neg' then do;
  _min_neg_date=min(_min_neg_date,From_Date);
  _min_neg_amt=sum(_min_neg_amt,abs(Amount));
  end;
 end;
 do _n_=1 to _n_ ;
  set have;
  if _f then continue;
  if From_Date=_last_positive_date and _min_neg_date>. then do;
  amount=_last_positive_amt-abs(_min_neg_amt);
  to_date=_min_neg_date;
  _f=1;
  end;
  output;
end;
drop _:;
run;

[NikosStratis]

This works fine!

Thank you very much for your quick (and valuable!!!) reply!

[novinosrin]

I would like to appreciate @Jagadishkatam 's thinking to make it so less verbose and convenient. While it's easy to understand, the thinking along those lines is indeed a great wit. Neat and original. There is one caveat, it might not be fastest(well, who's to say) when processing humongous dataset size, but by all means Kudos! to @Jagadishkatam . Cheers!

[novinosrin]

Hi @NikosStratis   how about this teaser 🙂

data have;
input Policy_No $ (From_Date To_Date)  (:date9.) Amount :comma10.2 Ind $;
format From_Date To_Date  date9.;
cards;
10864457 08Nov2013 08May2014 29,93 Pos
10864457 08May2014 08Nov2014 28,43 Pos
10864457 08Nov2014 08May2015 28,43 Pos
10864457 08May2015 08Nov2015 29,91 Pos
10864457 08Nov2015 08May2016 26,91 Pos
10864457 05Jan2016 08May2016 -18,39 Neg
10988488 23Sep2013 23Sep2014 63,89 Pos
11295729 04Dec2015 04Jun2016 38,69 Pos
11295729 10Feb2016 19Feb2016 -1,94 Neg
11295729 19Feb2016 04Jun2016 -22,56 Neg
;
proc sql;
create table want(drop=amt) as
select *,ifn(From_Date= max((ind='Pos')*From_Date),amt- sum((ind='Neg')*abs(amt)),amt) as Amount
from have(rename=(amount=amt))
group by  Policy_No
having ind='Pos'
order by Policy_No, From_date;
quit;

[NikosStratis]

Hi @novinosrin , first, let me thank you very much for your support.

There is still a problem with the To_Date that i need to be replaced. This was the issue I could not resolve.

Below is my initial description.

I need to merge the Neg values into the last Pos Value of each Policy (my key)
And replace the last To Date of the last Pos line with the min From Date of the Neg line(s).
My output shall look like this:
Policy_No From_Date To_Date Amount Ind
10864457 08Nov2013 08May2014 29,93
10864457 08May2014 08Nov2014 28,43
10864457 08Nov2014 08May2015 28,43
10864457 08May2015 08Nov2015 29,91
10864457 08Nov2015 05Jan2016 8,52
10988488 23Sep2013 23Sep2014 63,89
11295729 04Dec2015 10Feb2016 14,19

Thanks!

[novinosrin]

Hi @NikosStratis  Good morning.  I left that on purpose to make it interesting and interactive for you to figure out as it is just another copy/paste of the logic that uses min and ifn as opposed to max and ifn earlier. Hmm this gives me the impression you didn't read or understand the code. LOL

 Come on! bro! that's not fair. Anyways, 

data have;
input Policy_No $ (From_Date To_Date)  (:date9.) Amount :comma10.2 Ind $;
format From_Date To_Date  date9.;
cards;
10864457 08Nov2013 08May2014 29,93 Pos
10864457 08May2014 08Nov2014 28,43 Pos
10864457 08Nov2014 08May2015 28,43 Pos
10864457 08May2015 08Nov2015 29,91 Pos
10864457 08Nov2015 08May2016 26,91 Pos
10864457 05Jan2016 08May2016 -18,39 Neg
10988488 23Sep2013 23Sep2014 63,89 Pos
11295729 04Dec2015 04Jun2016 38,69 Pos
11295729 10Feb2016 19Feb2016 -1,94 Neg
11295729 19Feb2016 04Jun2016 -22,56 Neg
;

proc sql;
create table want(drop=amt min) as
select Policy_No,min(ifn(ind='Neg',From_Date,.)) as min,From_date,
ifn(From_Date= max((ind='Pos')*From_Date) and calculated min>.,calculated min,To_Date)
as To_Date format=date9. ,
ifn(From_Date= max((ind='Pos')*From_Date),amt- sum((ind='Neg')*abs(amt)),amt) as Amount,
ind
from have(rename=(amount=amt))
group by  Policy_No
having ind='Pos'
order by Policy_No, From_date;
quit;

[NikosStratis]

Hello @novinosrin 

Yes, you are right, I did not read the code...and I apologize for it!

What you sent me is exactly what i need!

Thanks again!

[novinosrin]

---

@NikosStratis wrote:

Hello @novinosrin 

Yes, you are right, I did not read the code...and I apologize for it!

What you sent me is exactly what i need!

Thanks again!

---

Need not apologize. I was just pulling your leg. It's fun. 🙂