BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasuser77
Calcite | Level 5

 

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!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

11 REPLIES 11
PGStats
Opal | Level 21

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

PG
sasuser77
Calcite | Level 5

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

Reeza
Super User

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

Ksharp
Super User
How do we know whether there is a active claim in history or not ?
sasuser77
Calcite | Level 5

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

 

 

 

Ksharp
Super User
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 ?

sasuser77
Calcite | Level 5

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.

Ksharp
Super User

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;
sasuser77
Calcite | Level 5

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.

Ksharp
Super User
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;
sasuser77
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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