How to adjust claims amount and generate a series based ion amounts for eligible months

[Nimish28]

Hi All,

I am working on a project where i have to adjust the claim amounts for certain scenarios and the data looks something like this

Subscriber_id startdt enddt amount
1 1/12021 3/14/2021 200
1 3/15/2021 . 300
2 1/1/2021 1/14/2021 400
3 1/15/2021 . 500

Now for subcriber_id =1 then amount should be adjusted using a calculation and the amount should be like
For the first two months the amount should be 200 for the next month it should be the calculated amount (ex 300/15) = 1500
And for rest of thw eligible months amount should be 300

For subscriber id=2 the amount should just be the calculated for 14 days (which is the end date) and amount should be 400/14 =28.57
For subscriber_if=3 the amount should
be calculated for first 14 days like 500/14
And for rest of the eligible months it should be 500.

I am trying to build a logic for the same but unable to do so.
Any help on this will be appreciated.

Thank you in advance

[sbxkoenk]

Hello,

For the example given, can you specify what should be the outcome (after data processing)?

That would make it easier for me (us) to come up with an appropriate program for this!

Thanks,

Koen

[Nimish28]

Hi,

Attaching a excel for reference. Please have a look at it.

Thanks

[HB]

I'm reasonably certain I don't understand your calculation method (months x rate but then rate divided by days- why???), but here is something to maybe get you thinking.  

Using Excel I can do this:

d	e	f	g	h	i	j	k	l	m
subscriber	start	end	amount	now	mdiff	cam	mddiff	cdam	tam
1	1/1/2021	3/14/2021	200	3/14/2021	2	400	14	14.29	414.29
1	3/15/2021		300	11/23/2021	8	2400	9	33.33	2433.33
2	1/1/2021	1/14/2021	400	1/14/2021	0	0	14	28.57	28.57
3	1/15/2021		500	11/23/2021	10	5000	11	45.45	5045.45

Now is the end date or the current date where the end date is empty.  

= IF(F7, F7,NOW())

mdiff is the difference between either the start and end or start and now in full months.

=IF(F7,DATEDIF(E7,F7,"m"),DATEDIF(E7,H7,"m"))

cam is a calculated monthly amount of full months times amount.

=+I7*G7

mddiff is the difference between either the start and end or start and now in days minus the full months. 

=IF(F7,DATEDIF(E7,F7,"md")+1, DATEDIF(E7,H7,"md")+1)

cdam is the calculated daily amount

=+G7/K7

tam is total amount and sum of cam and cdam

=+J7+L7

With data modified like that, I think the complete charge per subscriber is the sum of tam grouped by subscriber, which can be done with Proc Summary or Proc SQL or something.

Edit:  Apparently there are issues with MD.  Who knew? Looks fixable.

https://support.microsoft.com/en-us/office/datedif-function-25dba1a4-2812-480b-84dd-8b32a451b35c