BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
abhi309
Obsidian | Level 7

Hello SAS Gurus,

 

I am stuck again and need your help. I am working with prescription claims dataset and looking at adherence of a certain class of drugs in each patient. I am looking if the patients are filling this certain drug class for 84 days or more after the first fill but I also have a certain duration which is 114 days from the first prescription date (if we include the first prescription date this duration is 115 days).

 

Data set I have:

ID          First_Pres_Dt             Pres_Dt                              DAYS_SUPP

1             10OCT2022                10OCT2022                      30

1             10OCT2022                 26OCT2022                     30

1             10OCT2022                 30DEC2022                     30

1             10OCT2022                 01FEB2023                       30

1             10OCT2022                 11APR2023                       30

2            11AUG2022                   11AUG2022                      30

2            11AUG2022                   12SEP2022                       30

2             11AUG2022                  19DEC2022                      30

2              11AUG2022                 22JAN2022                      30

3              17AUG2022                 17AUG2022                      30

3               17AUG2022                 14SEP2022                      60

3              17AUG2022                  11DEC2022                       15

4              04OCT2022               04OCT2022                     15

4              04OCT2022               01NOV2022                      30

4              04OCT2022               15NOV2022                       30

4             04OCT2022                21JAN2023                        30

 

In the above dataset, if you look at ID 4, this pt. has first fill on 4th Oct 2022 and if I will add 114 days the date will be 26th Jan 2023. So this patient has to have at least 84 days of fill till 26th Jan 2023. But he is not meeting the criteria as he only has 81 days of fill (15+30+30+6), the last fill I am including only 6 days as his window is only up to 26th Jan 2023.  So pt. 4 is not meeting the criteria but rest of patients are meeting this criteria. Hope the explanation helps.

 

Thank you 

 

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

This code does what you ask for: it counts the number of "eligible" dates filled for each ID, where the eligible date range starts at the first PRES_DT and ends 114 days after FIRST_PRES_DT.  It accommodates gaps is supply and overlaps.  It assumes data are sorted by PRES_DT within ID.

 

data have;
input ID First_Pres_Dt :date9. Pres_Dt :date9. DAYS_SUPP;
format First_Pres_Dt date9. Pres_Dt date9. ;
datalines;
1 10OCT2022 10OCT2022 30
1 10OCT2022 26OCT2022 30
1 10OCT2022 30DEC2022 30
1 10OCT2022 01FEB2023 30
1 10OCT2022 11APR2023 30
2 11AUG2022 11AUG2022 30
2 11AUG2022 12SEP2022 30
2 11AUG2022 19DEC2022 30
2 11AUG2022 22JAN2022 30
3 17AUG2022 17AUG2022 30
3 17AUG2022 14SEP2022 60
3 17AUG2022 11DEC2022 15
4 04OCT2022 04OCT2022 15
4 04OCT2022 01NOV2022 30
4 04OCT2022 15NOV2022 30
4 04OCT2022 21JAN2023 30
run;

data want (keep=id first_pres_dt end_of_eligible_dates eligible_days_filled total_days_supply);
  do until (last.id);
    set have;
    by id ;
    format end_of_eligible_dates supply_start supply_end date9.;

    if first.id then do;
      supply_end = pres_dt-1;
      eligible_days_filled=0;
      total_days_supply=0;
    end;
    end_of_eligible_dates=intnx('day',first_pres_dt,114);
    total_days_supply+days_supp;

    supply_start = max(supply_end+1,pres_dt);
    if supply_start <= end_of_eligible_dates then eligible_days_filled = eligible_days_filled +  min(days_supp,end_of_eligible_dates+1-supply_start);
    supply_end=supply_end+days_supp;
  end;
run;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

7 REPLIES 7
mkeintz
PROC Star

This code does what you ask for: it counts the number of "eligible" dates filled for each ID, where the eligible date range starts at the first PRES_DT and ends 114 days after FIRST_PRES_DT.  It accommodates gaps is supply and overlaps.  It assumes data are sorted by PRES_DT within ID.

 

data have;
input ID First_Pres_Dt :date9. Pres_Dt :date9. DAYS_SUPP;
format First_Pres_Dt date9. Pres_Dt date9. ;
datalines;
1 10OCT2022 10OCT2022 30
1 10OCT2022 26OCT2022 30
1 10OCT2022 30DEC2022 30
1 10OCT2022 01FEB2023 30
1 10OCT2022 11APR2023 30
2 11AUG2022 11AUG2022 30
2 11AUG2022 12SEP2022 30
2 11AUG2022 19DEC2022 30
2 11AUG2022 22JAN2022 30
3 17AUG2022 17AUG2022 30
3 17AUG2022 14SEP2022 60
3 17AUG2022 11DEC2022 15
4 04OCT2022 04OCT2022 15
4 04OCT2022 01NOV2022 30
4 04OCT2022 15NOV2022 30
4 04OCT2022 21JAN2023 30
run;

data want (keep=id first_pres_dt end_of_eligible_dates eligible_days_filled total_days_supply);
  do until (last.id);
    set have;
    by id ;
    format end_of_eligible_dates supply_start supply_end date9.;

    if first.id then do;
      supply_end = pres_dt-1;
      eligible_days_filled=0;
      total_days_supply=0;
    end;
    end_of_eligible_dates=intnx('day',first_pres_dt,114);
    total_days_supply+days_supp;

    supply_start = max(supply_end+1,pres_dt);
    if supply_start <= end_of_eligible_dates then eligible_days_filled = eligible_days_filled +  min(days_supp,end_of_eligible_dates+1-supply_start);
    supply_end=supply_end+days_supp;
  end;
run;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
abhi309
Obsidian | Level 7

Hello @mkeintz,

 

Thank you for your response. I tried using the code you posted but it is not giving me accurate results. The final dataset has Eligible_Days_filled for pt. 1 is 1 and for pt. 2 is 0, which is not accurate.

mkeintz
PROC Star

@abhi309 wrote:

...

 

Thank you for your response. I tried using the code you posted but it is not giving me accurate results. The final dataset has Eligible_Days_filled for pt. 1 is 1 and for pt. 2 is 0, which is not accurate.


When I run the program I provided with the sample data you provided, included in my example, patient 1 gets 91 eligible days filled, patient 2 gets 85.  Please run the program I provided with the sample data in my response to confirm the results.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
abhi309
Obsidian | Level 7

@mkeintz you are correct when I ran the sample data I am getting the desired results when I am running the same program with the original data it is giving me different results. I copied the records first 3 patients as it is from my original data and therefore I am not understanding why I getting different results.

 

I checked the program twice but it is exactly the same.

 

Thank you

mkeintz
PROC Star

@abhi309 wrote:

@mkeintz you are correct when I ran the sample data I am getting the desired results when I am running the same program with the original data it is giving me different results. I copied the records first 3 patients as it is from my original data and therefore I am not understanding why I getting different results.

If the program is the same as I provided, then your original data must somehow be different than what you copied to this forum.  There can't be any other reason to get different results with the same program,


--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Patrick
Opal | Level 21

@abhi309 wrote:

@mkeintz you are correct when I ran the sample data I am getting the desired results when I am running the same program with the original data it is giving me different results. I copied the records first 3 patients as it is from my original data and therefore I am not understanding why I getting different results.

 

I checked the program twice but it is exactly the same.

 

Thank you


@abhi309 Just to make sure: You're running this code under SAS9.4 or Viya Compute and not multithreaded under Viya CAS - right?

abhi309
Obsidian | Level 7

Hello @mkeintz,

 

Sorry for the confusion and thank you again for the solution. I was using the dataset which was not sorted and that is why getting different results. 

 

Thank you

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 970 views
  • 3 likes
  • 3 in conversation