Identifying Unique Denied Claims within 14 days window.

Reply
Contributor
Posts: 21

Identifying Unique Denied Claims within 14 days window.

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

 

Patient_idDenied_dateBrand_nameGen_name
15/16/2017XIFAXANRIFAXIMIN
15/16/2017XIFAXANRIFAXIMIN
15/16/2017XIFAXANRIFAXIMIN
15/16/2017FLUCONAZOLEFLUCONAZOLE
15/16/2017FLUCONAZOLEFLUCONAZOLE
15/17/2017FLUCONAZOLEFLUCONAZOLE
15/18/2017FLUCONAZOLEFLUCONAZOLE
18/18/2017PRALUENT PENALIROCUMAB
18/31/2017PRALUENT PENALIROCUMAB
19/13/2017PRALUENT PENALIROCUMAB
19/15/2017PRALUENT PENALIROCUMAB
19/18/2017PRALUENT PENALIROCUMAB
19/21/2017PRALUENT PENALIROCUMAB
19/22/2017PRALUENT PENALIROCUMAB
19/25/2017PRALUENT PENALIROCUMAB
19/29/2017PRALUENT PENALIROCUMAB
110/20/2017PRALUENT PENALIROCUMAB
111/28/2017PRALUENT PENALIROCUMAB

Output should be:

 

Patient_idDenied_dateBrand_nameGen_name
15/16/2017XIFAXANRIFAXIMIN
15/18/2017FLUCONAZOLEFLUCONAZOLE
18/31/2017PRALUENT PENALIROCUMAB
19/25/2017PRALUENT PENALIROCUMAB
19/29/2017PRALUENT PENALIROCUMAB
110/20/2017PRALUENT PENALIROCUMAB
111/28/2017PRALUENT PENALIROCUMAB

 

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!

Respected Advisor
Posts: 3,067

Re: Identifying Unique Denied Claims within 14 days window.

[ Edited ]
Posted in reply to ssitharath0420

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;
    
--
Paige Miller
Contributor
Posts: 21

Re: Identifying Unique Denied Claims within 14 days window.

Posted in reply to PaigeMiller

Hi Page,

 

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

 

Sam

Respected Advisor
Posts: 3,067

Re: Identifying Unique Denied Claims within 14 days window.

Posted in reply to ssitharath0420

I just modified the code, try it again.

--
Paige Miller
Contributor
Posts: 21

Re: Identifying Unique Denied Claims within 14 days window.

Posted in reply to PaigeMiller

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.

Respected Advisor
Posts: 3,067

Re: Identifying Unique Denied Claims within 14 days window.

Posted in reply to ssitharath0420

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.

--
Paige Miller
Contributor
Posts: 21

Re: Identifying Unique Denied Claims within 14 days window.

Posted in reply to PaigeMiller

This is the result:

 

Patient_idDenied_dateBrand_nameGen_name
15/16/2017XIFAXANRIFAXIMIN
15/18/2017FLUCONAZOLEFLUCONAZOLE
18/31/2017PRALUENT PENALIROCUMAB
19/25/2017PRALUENT PENALIROCUMAB
19/29/2017PRALUENT PENALIROCUMAB
110/20/2017PRALUENT PENALIROCUMAB
111/28/2017PRALUENT PENALIROCUMAB
Respected Advisor
Posts: 3,067

Re: Identifying Unique Denied Claims within 14 days window.

Posted in reply to ssitharath0420

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

--
Paige Miller
Contributor
Posts: 21

Re: Identifying Unique Denied Claims within 14 days window.

Posted in reply to PaigeMiller

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.

Super User
Posts: 13,583

Re: Identifying Unique Denied Claims within 14 days window.

Posted in reply to ssitharath0420

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

 

PROC Star
Posts: 1,845

Re: Identifying Unique Denied Claims within 14 days window.

Posted in reply to ssitharath0420

 

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;

 

PROC Star
Posts: 1,845

Re: Identifying Unique Denied Claims within 14 days window.

[ Edited ]
Posted in reply to 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

 

 

Contributor
Posts: 21

Re: Identifying Unique Denied Claims within 14 days window.

Posted in reply to novinosrin

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

PROC Star
Posts: 1,845

Re: Identifying Unique Denied Claims within 14 days window.

Posted in reply to ssitharath0420

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

Ask a Question
Discussion stats
  • 13 replies
  • 121 views
  • 0 likes
  • 4 in conversation