DATA Step, Macro, Functions and more

How to check if next claim get paid or rejected

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

How to check if next claim get paid or rejected

 

Hi,

I have to identify if the next claim is paid or rejected look at historical data of that member. I have dataset  as shown below.

The rule is the next claim gets paid if the sum of any active claims MED in history + new claim MED doesn't exceeds 200.

The claim will be active for starting its fill_date to the 75% of its total days supply( rounded below).

ID

Days_Supply

Fill_dt

MED

CLM_START_DTE

CLM_END_DTE

1

30

1/21/2015

180.00

1/21/2015

2/19/2015

1

30

1/21/2015

30.00

1/21/2015

2/19/2015

1

20

1/21/2015

4.50

1/21/2015

2/9/2015

1

30

2/28/2015

30.00

2/28/2015

3/28/2015

1

20

2/28/2015

4.50

2/28/2015

3/19/2015

1

30

3/29/2015

30.00

3/29/2015

4/25/2015

1

20

4/13/2015

100.00

4/13/2015

5/2/2015

1

30

4/15/2015

80.00

4/26/2015

5/4/2015

2

25

2/16/2015

30

2/16/2015

3/12/2015

2

25

4/15/2015

30

4/15/2015

5/9/2015

2

10

7/1/2015

56

7/1/2015

7/10/2015

2

4

7/5/2015

180

7/8/2015

8/8/2015

 

and wanted to see data as below. New column with claim status whether it gets Paid or Rejected.

ID

Days_Supply

Fill_dt

MED

CLM_START_DTE

CLM_END_DTE

Clm_Deactive_Date

Claim_Stts

1

30

1/21/2015

180.00

1/21/2015

2/19/2015

2/12/2015

Pays

1

30

1/21/2015

30.00

1/21/2015

2/19/2015

2/12/2015

Rejects  as there is a active claim in history  and Med ecceeds 200(180+30)

1

20

1/21/2015

4.50

1/21/2015

2/9/2015

2/5/2015

Pays 180+4.5 < 200

1

30

2/28/2015

30.00

2/28/2015

3/28/2015

3/22/2015

Pays ( No active claim in history and  30 < 200)

1

20

2/28/2015

4.50

2/28/2015

3/19/2015

3/15/2015

Pays (30+4.5 < 200)

1

30

3/29/2015

30.00

3/29/2015

4/25/2015

4/20/2015

Pays (No active claim in history 30 < 200)

1

20

4/13/2015

100.00

4/13/2015

5/2/2015

4/28/2015

Pays (100+30 < 200)

1

30

4/15/2015

80.00

4/26/2015

5/4/2015

5/7/2015

Rejects (Med ecceeds 200(30+100+80) two claims active in history

2

25

2/16/2015

30

2/16/2015

3/12/2015

3/6/2015

Pays

2

25

4/15/2015

30

4/15/2015

5/9/2015

5/3/2015

Pays

2

10

7/1/2015

56

7/1/2015

7/10/2015

7/8/2015

Pays

2

4

7/5/2015

180

7/8/2015

8/8/2015

7/8/2015

Rejects (56+180 exceeds 200)

 

 

Thanks in advance for help!

 

 


Accepted Solutions
Solution
‎06-13-2016 07:36 AM
Super User
Posts: 9,681

Re: How to check if next claim get paid or rejected

data have;
input 
ID
Days_Supply
Fill_dt : mmddyy10.
MED
CLM_START_DTE  : mmddyy10.
CLM_END_DTE  : mmddyy10.
Clm_Deactive_Date  : mmddyy10.;
format Fill_dt CLM_START_DTE  CLM_END_DTE  Clm_Deactive_Date  mmddyy10.;
cards;
1
30
1/21/2015
180.00
1/21/2015
2/19/2015
2/12/2015
1
30
1/21/2015
30.00
1/21/2015
2/19/2015
2/12/2015
1
20
1/21/2015
4.50
1/21/2015
2/9/2015
2/5/2015
1
30
2/28/2015
30.00
2/28/2015
3/28/2015
3/22/2015
1
20
2/28/2015
4.50
2/28/2015
3/19/2015
3/15/2015
1
30
3/29/2015
30.00
3/29/2015
4/25/2015
4/20/2015
1
20
4/13/2015
100.00
4/13/2015
5/2/2015
4/28/2015
1
30
4/15/2015
80.00
4/26/2015
5/4/2015
5/7/2015
2
25
2/16/2015
30
2/16/2015
3/12/2015
3/6/2015
2
25
4/15/2015
30
4/15/2015
5/9/2015
5/3/2015
2
10
7/1/2015
56
7/1/2015
7/10/2015
7/8/2015
2
4
7/5/2015
180
7/8/2015
8/8/2015
7/8/2015
;
run;
data want;
array x{99999} _temporary_;
array y{99999} _temporary_;
array z{99999} $ 8 _temporary_;
call missing(of x{*} y{*} z{*});
length Claim_Stts $ 8;

 do i=1 by 1 until(last.id);
  set have;
  by id;
  x{i}=Clm_Deactive_Date;
  y{i}=MED;
 end;
 
 do j=1 by 1 until(last.id);
  set have;
  by id;
  pays=0;
  do k=1 to j;
   if Fill_dt lt x{k} and z{k} ne 'Rejects' then pays+y{k};
  end;
  if pays lt 200 then Claim_Stts='Pays';
   else Claim_Stts='Rejects';
  z{j}=Claim_Stts;
  output;
 end;
drop i j k pays;
run;

View solution in original post


All Replies
Respected Advisor
Posts: 4,649

Re: How to check if next claim get paid or rejected

How do you know in which order to process claims that have the same fill_dt?

PG
Occasional Contributor
Posts: 17

Re: How to check if next claim get paid or rejected

Could you assume that my data is already in the order of its processing.

Super User
Posts: 17,829

Re: How to check if next claim get paid or rejected

How do you know if a claim is 'active'?

Super User
Posts: 9,681

Re: How to check if next claim get paid or rejected

How do we know whether there is a active claim in history or not ?
Occasional Contributor
Posts: 17

Re: How to check if next claim get paid or rejected

A claim is active from its fill_dt to the 75% of Days_supply rounded to lowest interger value.

 

for below example

ID

Days_Supply

Fill_dt

MED

CLM_START_DTE

CLM_END_DTE

1

30

1/21/2015

180.00

1/21/2015

2/19/2015

 

claim is active for floor(30*.75) = 22 days from fill_Dt

 

1/21/2015 + 22 days i.e(2/11/2015) this claim will be active.

 

ID

Days_Supply

Fill_dt

MED

CLM_START_DTE

CLM_END_DTE

Clm_Deactive_Date

Claim_Stts

1

30

1/21/2015

180.00

1/21/2015

2/19/2015

2/12/2015

Pays

 

 

 

Super User
Posts: 9,681

Re: How to check if next claim get paid or rejected

No. I mean.


1
30
3/29/2015
30.00
3/29/2015
4/25/2015
4/20/2015
Pays (No active claim in history 30 < 200)
---- Why would there be No active claim in history?


1
20
4/13/2015
100.00
4/13/2015
5/2/2015
4/28/2015
Pays (100+30 < 200)
----Why 30 should be active claim in history ?



1
30
4/15/2015
80.00
4/26/2015
5/4/2015
5/7/2015
Rejects (Med ecceeds 200(30+100+80) two claims active in history
-----Why 30,100 should be active claim in history ?



2
25
2/16/2015
30
2/16/2015
3/12/2015
3/6/2015
Pays
----Why is there no active claim in history ?

Occasional Contributor
Posts: 17

Re: How to check if next claim get paid or rejected

Sorry, I could'nt get your question.

 

Think off in this way. At any point of time a member can't exceed the dosage of 200. Exception is if his re-fill is after 75% exhaust of his dosage(ex: if member had fill for 10 days and he next filled on 8th day from his fill date we are ignoring) other wise we are summing up his previous dosages that are active and current dosage and see if it exceeds 200. One more thing if a member no past claim and his present fill dosage > 200 that is still rejected.

Super User
Posts: 9,681

Re: How to check if next claim get paid or rejected

OK. Assuming I understand what you are talking about .

History Claim is when Fill_dt < Clm_Deactive_Date  .

 

data have;
input 
ID
Days_Supply
Fill_dt : mmddyy10.
MED
CLM_START_DTE  : mmddyy10.
CLM_END_DTE  : mmddyy10.
Clm_Deactive_Date  : mmddyy10.;
format Fill_dt CLM_START_DTE  CLM_END_DTE  Clm_Deactive_Date  mmddyy10.;
cards;
1
30
1/21/2015
180.00
1/21/2015
2/19/2015
2/12/2015
1
30
1/21/2015
30.00
1/21/2015
2/19/2015
2/12/2015
1
20
1/21/2015
4.50
1/21/2015
2/9/2015
2/5/2015
1
30
2/28/2015
30.00
2/28/2015
3/28/2015
3/22/2015
1
20
2/28/2015
4.50
2/28/2015
3/19/2015
3/15/2015
1
30
3/29/2015
30.00
3/29/2015
4/25/2015
4/20/2015
1
20
4/13/2015
100.00
4/13/2015
5/2/2015
4/28/2015
1
30
4/15/2015
80.00
4/26/2015
5/4/2015
5/7/2015
2
25
2/16/2015
30
2/16/2015
3/12/2015
3/6/2015
2
25
4/15/2015
30
4/15/2015
5/9/2015
5/3/2015
2
10
7/1/2015
56
7/1/2015
7/10/2015
7/8/2015
2
4
7/5/2015
180
7/8/2015
8/8/2015
7/8/2015
;
run;
data want;
array x{99999} _temporary_;
array y{99999} _temporary_;
call missing(of x{*} y{*});
length Claim_Stts $ 8;

 do i=1 by 1 until(last.id);
  set have;
  by id;
  x{i}=Clm_Deactive_Date;
  y{i}=MED;
 end;
 
 do j=1 by 1 until(last.id);
  set have;
  by id;
  pays=0;
  do k=1 to j;
   if Fill_dt lt x{k} then pays+y{k};
  end;
  if pays lt 200 then Claim_Stts='Pays';
   else Claim_Stts='Rejects  ';
  output;
 end;
drop i j k pays;
run;
Occasional Contributor
Posts: 17

Re: How to check if next claim get paid or rejected

Thank you Xia, it seems working as expected. except third observation shouldn't have been Rejected.

 

Claim_SttsIDDays_SupplyFill_dtMEDCLM_START_DTECLM_END_DTEClm_Deactive_Date
Pays1301/21/20151801/21/20152/19/20152/12/2015
Rejects1301/21/2015301/21/20152/19/20152/12/2015
Rejects1201/21/20154.51/21/20152/9/20152/5/2015

 

since you already rejected 2nd observation. Total MED for 3rd observation would be 180+4.5 < 200 and should be paid.

Solution
‎06-13-2016 07:36 AM
Super User
Posts: 9,681

Re: How to check if next claim get paid or rejected

data have;
input 
ID
Days_Supply
Fill_dt : mmddyy10.
MED
CLM_START_DTE  : mmddyy10.
CLM_END_DTE  : mmddyy10.
Clm_Deactive_Date  : mmddyy10.;
format Fill_dt CLM_START_DTE  CLM_END_DTE  Clm_Deactive_Date  mmddyy10.;
cards;
1
30
1/21/2015
180.00
1/21/2015
2/19/2015
2/12/2015
1
30
1/21/2015
30.00
1/21/2015
2/19/2015
2/12/2015
1
20
1/21/2015
4.50
1/21/2015
2/9/2015
2/5/2015
1
30
2/28/2015
30.00
2/28/2015
3/28/2015
3/22/2015
1
20
2/28/2015
4.50
2/28/2015
3/19/2015
3/15/2015
1
30
3/29/2015
30.00
3/29/2015
4/25/2015
4/20/2015
1
20
4/13/2015
100.00
4/13/2015
5/2/2015
4/28/2015
1
30
4/15/2015
80.00
4/26/2015
5/4/2015
5/7/2015
2
25
2/16/2015
30
2/16/2015
3/12/2015
3/6/2015
2
25
4/15/2015
30
4/15/2015
5/9/2015
5/3/2015
2
10
7/1/2015
56
7/1/2015
7/10/2015
7/8/2015
2
4
7/5/2015
180
7/8/2015
8/8/2015
7/8/2015
;
run;
data want;
array x{99999} _temporary_;
array y{99999} _temporary_;
array z{99999} $ 8 _temporary_;
call missing(of x{*} y{*} z{*});
length Claim_Stts $ 8;

 do i=1 by 1 until(last.id);
  set have;
  by id;
  x{i}=Clm_Deactive_Date;
  y{i}=MED;
 end;
 
 do j=1 by 1 until(last.id);
  set have;
  by id;
  pays=0;
  do k=1 to j;
   if Fill_dt lt x{k} and z{k} ne 'Rejects' then pays+y{k};
  end;
  if pays lt 200 then Claim_Stts='Pays';
   else Claim_Stts='Rejects';
  z{j}=Claim_Stts;
  output;
 end;
drop i j k pays;
run;
Occasional Contributor
Posts: 17

Re: How to check if next claim get paid or rejected

Perfect, Thank you! This is exactly what I was looking for.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 426 views
  • 0 likes
  • 4 in conversation