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!
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;
How do you know in which order to process claims that have the same fill_dt?
Could you assume that my data is already in the order of its processing.
How do you know if a claim is 'active'?
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 |
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 ?
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.
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;
Thank you Xia, it seems working as expected. except third observation shouldn't have been Rejected.
Claim_Stts | ID | Days_Supply | Fill_dt | MED | CLM_START_DTE | CLM_END_DTE | Clm_Deactive_Date |
Pays | 1 | 30 | 1/21/2015 | 180 | 1/21/2015 | 2/19/2015 | 2/12/2015 |
Rejects | 1 | 30 | 1/21/2015 | 30 | 1/21/2015 | 2/19/2015 | 2/12/2015 |
Rejects | 1 | 20 | 1/21/2015 | 4.5 | 1/21/2015 | 2/9/2015 | 2/5/2015 |
since you already rejected 2nd observation. Total MED for 3rd observation would be 180+4.5 < 200 and should be paid.
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;
Perfect, Thank you! This is exactly what I was looking for.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.