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 |
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;
@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;
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 |
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.