topic Re: Identifying Unique Denied Claims within 14 days window. in SAS Programming

Identifying Unique Denied Claims within 14 days window.

ssitharath0420 — Wed, 20 Jun 2018 15:06:06 GMT

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!

Re: Identifying Unique Denied Claims within 14 days window.

PaigeMiller — Wed, 20 Jun 2018 15:35:44 GMT

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;

Re: Identifying Unique Denied Claims within 14 days window.

ssitharath0420 — Wed, 20 Jun 2018 15:33:43 GMT

Hi Page,

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

Sam

Re: Identifying Unique Denied Claims within 14 days window.

PaigeMiller — Wed, 20 Jun 2018 15:35:03 GMT

I just modified the code, try it again.

Re: Identifying Unique Denied Claims within 14 days window.

ssitharath0420 — Wed, 20 Jun 2018 16:09:50 GMT

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.

Re: Identifying Unique Denied Claims within 14 days window.

PaigeMiller — Wed, 20 Jun 2018 16:19:22 GMT

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-data-AKA-generate/ta-p/258712

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

Re: Identifying Unique Denied Claims within 14 days window.

ssitharath0420 — Wed, 20 Jun 2018 16:30:30 GMT

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

Re: Identifying Unique Denied Claims within 14 days window.

ballardw — Wed, 20 Jun 2018 16:35:19 GMT

@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

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.

Re: Identifying Unique Denied Claims within 14 days window.

PaigeMiller — Wed, 20 Jun 2018 16:38:12 GMT

@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.

Re: Identifying Unique Denied Claims within 14 days window.

novinosrin — Wed, 20 Jun 2018 17:01:15 GMT

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;

Re: Identifying Unique Denied Claims within 14 days window.

novinosrin — Wed, 20 Jun 2018 17:09:41 GMT

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

Re: Identifying Unique Denied Claims within 14 days window.

ssitharath0420 — Wed, 20 Jun 2018 21:08:31 GMT

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

Re: Identifying Unique Denied Claims within 14 days window.

novinosrin — Wed, 20 Jun 2018 21:12:41 GMT

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

Re: Identifying Unique Denied Claims within 14 days window.

ssitharath0420 — Thu, 21 Jun 2018 14:14:38 GMT

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.