How to calculate length of drug supply of mutiple fill dates

[kp2519]

Hi I am working with medical claims data with members have multiple different fills and supply according to the fills. I want to add each fill length to the fill date to create new field called end date. I having trouble running an array for this situation. I sorted my data and transposed but my array isnt working. Hoping to get help on how to solve this issues. Would appreciate any help!

data array_trial;
set have;
array end_date{3} end_date1-end_date3;
array fill{3} fill_date1-fill_date3;
array supply{26} days_supply1-days_supply3;

do ii=1 to 3; script{ii}=0; end;

do ii=1 to 26;
do i=1 to 26;
script{ii}= intnx('DTDAY',fill{i},supply{i});
drop i ii;
end;
end;
run;

What i currently have is :

Have
Mbr_ID	Fill_date	Days_supply
1234	10/10/2019	20
1234	10/30/2019	30
1234	11/12/2019	20
1235	10/11/2019	20
1235	10/12/2019	30
1235	11/13/2019	20

want
mbr_id	fill_date1	fill_date2	fill_date3	days_supply1	days_supply2	days_supply3	end_date1	end_date2	end_date3
1234	10/10/2019	10/30/2019	11/12/2019	20	30	20	10/30/2109	x
1235	10/11/2019	10/12/2019	11/13/2019	20	30	20

[Patrick]

Transposing multiple variables at once is a bit of a challenge when using Proc Transpose. Fortunately there is now a well documented macro %transpose() available which does such jobs just beautifully. 

The macro adds a lot of code - but you can just copy/paste it from here as is without having to change anything. Once you've got the macro definition executed the remaining code can look like:

data Have;
  infile datalines dlm=' ' truncover;
  input Mbr_ID $ Fill_date:mmddyy10. Days_supply;
  format Fill_date date9.;
  datalines;
1234 10/10/2019 20
1234 10/30/2019 30
1234 11/12/2019 20
1235 10/11/2019 20
1235 10/12/2019 30
1235 11/13/2019 20
;

data inter;
  set have;
  format end_date date9.;
  end_date=fill_date+days_supply;
run;

%transpose(data=inter, out=want, by=mbr_id, var=Fill_date Days_supply end_date, sort=yes, guessingrows=1000);

proc print;
run;

[Reeza]

In general, it's better to keep your data in a long format. What are you trying to do overall? If you explain that we can likely show how to do it within this current data structure.

[kp2519]

I am trying to see the impact if their is a rule implemented would reject any opioid claim if it was filled within 30 days plus days supply of the suboxone.
My idea was first to get all the claims of suboxone and calculate the what the end date is.
Then merge the list in of all the other opioid claim and create a flag to see how many rejected claims would be from subsequent claims...

Is my thought process right or would you have a better idea working with the data structure

[Reeza]

Yeah, I would post that question directly with sample data and expected output. A SQL query is what's needed here instead.

[ballardw]

---

@kp2519 wrote:
I am trying to see the impact if their is a rule implemented would reject any opioid claim if it was filled within 30 days plus days supply of the suboxone.
My idea was first to get all the claims of suboxone and calculate the what the end date is.
Then merge the list in of all the other opioid claim and create a flag to see how many rejected claims would be from subsequent claims...

Is my thought process right or would you have a better idea working with the data structure

---

See if this gets you started. I modified your example data so that at least one record did not meet the 30 days plus days_supply from the previous.

This sets a variable named flag to 1 when that condition is met.

data have;
input Mbr_ID $ Fill_date :mmddyy10. Days_supply ;
format fill_date mmddyy10.;
datalines;
1234 08/10/2019 20 
1234 10/30/2019 30 
1234 11/12/2019 20 
1235 10/11/2019 20 
1235 10/12/2019 30 
1235 11/13/2019 20 
;
/* assumes sorted by id and date*/
data want;
   set have;
   by Mbr_ID;
   ldate=lag(fill_date);
   ldays=lag(days_supply); 
   if first.mbr_id then ;
   else if fill_date le (ldate + ldays +30) then Flag=1;
   format ldate mmddyy10.;
   drop ldate ldays;
run; 

[kp2519]

Hi thanks for the suggestion for the data structure and management, It was really helpful with the data set.  Can you explain the lag function a little bit i am alittle confused. Maybe i also did not present my question as clearly as i wanted as well.

From your suggestions this is where i am at now .

I sorted the data for mbr_id and fill_date and i created a flag for those who have suboxone and opioid.  From here how would i use the lag function to calculate if the opioid claim is rejected if its 30 days +days supply of my last suboxone fill?

thanks for all the help it, ive learned alot 

Have

mbr_id	fill_date	days	sub_flag
1	06Jan2018	15.000	suboxone
1	20Jan2018	15.000	suboxone
1	03Feb2018	15.000	suboxone
1	23Feb2018	3.000	opioid
1	24Feb2018	15.000	suboxone
1	24May2018	7.000	suboxone
2	03Apr2018	5.000	suboxone
2	07Apr2018	30.000	suboxone
2	02May2018	30.000	opioid

want
mbr_id	fill_date	days	sub_flag	reject_flag
1	06Jan2018	15.000	suboxone
1	20Jan2018	15.000	suboxone
1	03Feb2018	15.000	suboxone
1	23Feb2018	3.000	opioid	1
1	24Feb2018	15.000	suboxone
1	24May2018	7.000	suboxone
2	03Apr2018	5.000	suboxone
2	07Apr2018	30.000	suboxone
2	02Sep2018	30.000	opioid	0