Identifying Unique Denied Claims within 14 days window.

[ssitharath0420]

I am trying to identify unique all denied claims within a 14 days window.

Patient_id	Denied_date	Brand_name	Gen_name
1	5/16/2017	XIFAXAN	RIFAXIMIN
1	5/16/2017	XIFAXAN	RIFAXIMIN
1	5/16/2017	XIFAXAN	RIFAXIMIN
1	5/16/2017	FLUCONAZOLE	FLUCONAZOLE
1	5/16/2017	FLUCONAZOLE	FLUCONAZOLE
1	5/17/2017	FLUCONAZOLE	FLUCONAZOLE
1	5/18/2017	FLUCONAZOLE	FLUCONAZOLE
1	8/18/2017	PRALUENT PEN	ALIROCUMAB
1	8/31/2017	PRALUENT PEN	ALIROCUMAB
1	9/13/2017	PRALUENT PEN	ALIROCUMAB
1	9/15/2017	PRALUENT PEN	ALIROCUMAB
1	9/18/2017	PRALUENT PEN	ALIROCUMAB
1	9/21/2017	PRALUENT PEN	ALIROCUMAB
1	9/22/2017	PRALUENT PEN	ALIROCUMAB
1	9/25/2017	PRALUENT PEN	ALIROCUMAB
1	9/29/2017	PRALUENT PEN	ALIROCUMAB
1	10/20/2017	PRALUENT PEN	ALIROCUMAB
1	11/28/2017	PRALUENT PEN	ALIROCUMAB

Output should be:

Patient_id	Denied_date	Brand_name	Gen_name
1	5/16/2017	XIFAXAN	RIFAXIMIN
1	5/18/2017	FLUCONAZOLE	FLUCONAZOLE
1	8/31/2017	PRALUENT PEN	ALIROCUMAB
1	9/25/2017	PRALUENT PEN	ALIROCUMAB
1	9/29/2017	PRALUENT PEN	ALIROCUMAB
1	10/20/2017	PRALUENT PEN	ALIROCUMAB
1	11/28/2017	PRALUENT PEN	ALIROCUMAB

The code I used is this:

data denied_reassign1;
set denied_reassign;
enddate=denied_adj_dte+14;
format enddate date9.;
run;
proc sort data=denied_reassign1;
 by denied_patid denied_genname denied_adj_dte;
 run;

data denied_reassign2;
set denied_reassign1;
patient_id2=lag1(denied_patid);
GenName2=lag1(Denied_Genname);
o_reject_cd2=lag1(o_reject_cd);
internal_resp_cd2=lag1(internal_resp_cd1);
denied_adj_dte_2=lag1(Denied_Adj_Dte);
format denied_adj_dte_2 date9.;
run;

proc sort data=denied_reassign2;
 by denied_patid denied_genname denied_adj_dte;
 run;

data denied_reassign3;
set denied_reassign2;
Diff=denied_adj_dte-denied_adj_dte_2;
D_Adj_Dte=denied_adj_dte;
format D_Adj_Dte date9.;
run;

proc sort data=denied_reassign3;
 by denied_patid denied_genname denied_adj_dte;
 run;

data Rolling14Day_assgn;
set denied_reassign3;
if patient_id2=denied_patid and GenName2=Denied_Genname 
and Diff<=14 then do;
denied_adj_dte=end2;
end2=max(enddate,end2);
end;
else do;
seg+1;
end2=enddate;
end;
format end2 date9.;
run;

proc sort data=Rolling14Day_assgn;
 by denied_patid denied_genname seg;
 run;

data Retain_Last_Clms;
set Rolling14Day_assgn;
by denied_patid denied_genname seg;
if last.seg then Keep=1;
else if Keep^=1 then delete;
drop denied_adj_dte;
run;

Some patients outputs were return correctly but other did not like the example I provided.  Please help!

[PaigeMiller]

UNTESTED CODE

proc sort data=have;
    by patient_id brand_name gen_name denied_date;
run;
data want;
    retain last_day_of_14_day_period;
    set have;
    by  patient_id brand_name gen_name;
    if first.gen_name then last_day_of_14_day_period=denied_date+13;
    if denied_date<=last_day_of_14_day_period and not first.gen_name then delete;
    else if not first.gen_name then last_day_of_14_day_period=denied_date+13;
run;
    

[ssitharath0420]

Hi Page,

Thank you for the code but it only provide the first gen_name and it omitted all the other denied drugs.

Sam

[PaigeMiller]

I just modified the code, try it again.

[ssitharath0420]

That didn't work either. It just gave me all the denied claims with denied_adj_dt+13.

I need to tell it once the first denied drug is completed to move to the next denied drug.

[PaigeMiller]

Okay, we're not making progress

so a couple of things will help us make progress

You need to provide your data in the form of SAS code, so the rest of us can use it and try out our program on it

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

You also need to SHOW US the resulting data set so we can see what you are seeing. A screen capture is fine.

[ssitharath0420]

This is the result:

Patient_id	Denied_date	Brand_name	Gen_name
1	5/16/2017	XIFAXAN	RIFAXIMIN
1	5/18/2017	FLUCONAZOLE	FLUCONAZOLE
1	8/31/2017	PRALUENT PEN	ALIROCUMAB
1	9/25/2017	PRALUENT PEN	ALIROCUMAB
1	9/29/2017	PRALUENT PEN	ALIROCUMAB
1	10/20/2017	PRALUENT PEN	ALIROCUMAB
1	11/28/2017	PRALUENT PEN	ALIROCUMAB

[PaigeMiller]

---

@ssitharath0420 wrote:

This is the result:

---

Do you mean this is the result of running my code?????? It's not clear. Or is this the result you want???? Please clarify.

If it is the result of running my code, this is exactly what you asked for in your original message.

[ssitharath0420]

This is the result I want.  Your code works great but it only take the first drug of that patient. I need it to look all the patient I have in my data set with their corresponding drug.

[ballardw]

---

@ssitharath0420 wrote:

That didn't work either. It just gave me all the denied claims with denied_adj_dt+13.

I need to tell it once the first denied drug is completed to move to the next denied drug.

---

What does "completed" mean? I do not see any requirement or comment about "completed" or "first denied drug" in the initial question.

I am also not sure how you are defining "14 day window". You might have some internal definition that we do not know.

I can't even be sure that your "denied_date" is an actual SAS date valued variable or not.

Where you show

1

9/25/2017

PRALUENT PEN

ALIROCUMAB

I am not sure why that is included in the desired output but

1	9/22/2017	PRALUENT PEN	ALIROCUMAB

isn't.

[novinosrin]

data have;
input Patient_id 	Denied_date :mmddyy10.	Brand_name	& $20. Gen_name :$20.;
format Denied_date mmddyy10.;
cards;
1	5/16/2017	XIFAXAN	 RIFAXIMIN
1	5/16/2017	XIFAXAN	 RIFAXIMIN
1	5/16/2017	XIFAXAN	 RIFAXIMIN
1	5/16/2017	FLUCONAZOLE	  FLUCONAZOLE
1	5/16/2017	FLUCONAZOLE	  FLUCONAZOLE
1	5/17/2017	FLUCONAZOLE	  FLUCONAZOLE
1	5/18/2017	FLUCONAZOLE	  FLUCONAZOLE
1	8/18/2017	PRALUENT PEN	ALIROCUMAB
1	8/31/2017	PRALUENT PEN	ALIROCUMAB
1	9/13/2017	PRALUENT PEN	ALIROCUMAB
1	9/15/2017	PRALUENT PEN	ALIROCUMAB
1	9/18/2017	PRALUENT PEN	ALIROCUMAB
1	9/21/2017	PRALUENT PEN	ALIROCUMAB
1	9/22/2017	PRALUENT PEN	ALIROCUMAB
1	9/25/2017	PRALUENT PEN	ALIROCUMAB
1	9/29/2017	PRALUENT PEN	ALIROCUMAB
1	10/20/2017	PRALUENT PEN	ALIROCUMAB
1	11/28/2017	PRALUENT PEN	ALIROCUMAB
;

data temp;
set have;
by Patient_id;
retain _k;
if first.Patient_id then do;call missing(_k);_k=intnx('day',Denied_date,14); group=1;end;
else if not _f and Denied_date>_k then do; group+1;_f=1;_k=intnx('day',Denied_date,14);end;
format _k mmddyy10.;
drop _:;
run;

proc sql;
create table want(drop=group) as
select distinct *
from temp
group by patient_id, group, brand_name,gen_name
having Denied_date=max(Denied_date)
order by patient_id,Denied_date;
quit;

[novinosrin]

Hi Good afternoon @ssitharath0420  Although I had fun playing around with your data. I would like to opine that your data needs solid and  neat pre processing with a full stack filled time series with continuous dates so that the process becomes a whole lot easier and easy to maintain. 

If you are looking to implement a well automated code to run in production server, blimey!! that's not the data I would be happy to work with. --> My 2 cents

[ssitharath0420]

It is strange, it works for some patients but not for other.

[novinosrin]

I can only test with the sample you post. May be you could try tweaking to work for your real data