BookmarkSubscribeRSS Feed
ssitharath0420
Quartz | Level 8

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!

13 REPLIES 13
PaigeMiller
Diamond | Level 26

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
ssitharath0420
Quartz | Level 8

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
Diamond | Level 26

I just modified the code, try it again.

--
Paige Miller
ssitharath0420
Quartz | Level 8

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
Diamond | Level 26

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
ssitharath0420
Quartz | Level 8

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
PaigeMiller
Diamond | Level 26

@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
ssitharath0420
Quartz | Level 8

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

@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
Tourmaline | Level 20

 

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
Tourmaline | Level 20

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
Quartz | Level 8

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

novinosrin
Tourmaline | Level 20

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

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
  • 13 replies
  • 741 views
  • 0 likes
  • 4 in conversation