Identifying the next date observation in data step, help

[suncawy]

Hello,

      I can't think of a solution. I'm a HEDIS measure analysis, where we have to track when a patient has been on 2 or more medications during the year. I've been able to identify

the first sequence where they were on the meds, but I'm having problems getting the subsequent dates when they were on it.  I just can't seem to think of a way to get to it (having "writers block").

So,  the data looks like this:

  PatID      Med         Disp dt      Run Out date

    A           abilify       1/7/2014       4/17/2014

    A           latuda       1/22/2014     7/5/2014

    A           abilify       5/16/2014      6/17/2014

    A           latuda       5/20/2014      10/17/2014

    A           risp           8/1/2014      9/2/2014

    B           clan          2/20/2014     5/2/2014

    B           zipra         2/24/2014    4/24/2014

    B           risp           5/6/2014      6/7/2014

    B           clan           6/10/201      7/15/2014

  The final data should have these data in it:

PatID      Med         Disp dt      Run Out date

    A           abilify       1/7/2014       4/17/2014

    A           latuda       1/22/2014     7/5/2014

    A           abilify       5/16/2014      6/17/2014

    A           latuda       5/20/2014      10/17/2014

    B           clan          2/20/2014     5/2/2014

    B           zipra         2/24/2014    4/24/2014

  The patients where they had 2 or more meds during the year and excluding where they didn't.

Any ideas ?   Thank you in advance.

[Reeza]

1. Create your subset table of where you have the duplicate drugs per ID

2. Join back to original table

You can do it in a single sql, but I'll leave that step to you.

proc sql;

create table multiple_drugs as

select patid, med

from have

group by patid, med

having count(med)>=2;

quit;

proc sql;

create table detail_table as

select a.*

from have a

join multiple_drugs b

on a.patid=b.patid

and a.med=b.med;

quit;

[PGStats]

Are you looking for periods where patients were on two medications at the same time or for years where patients have taken two or more medications? - PG

[suncawy]

It would be periods where patients were on two medications at the same time.  For example, patient A is on 2 medications from about January through April. Then there could be break and the patient starts on 2 meds in May.  I can get the first instance where this occurs,  but I can't seem to think of a way to get the subsequent periods. 

[PGStats]

You could start with something like:

proc sql;

create table drugCombinations as

select a.PatID,

    catx(" - ", a.Med, b.Med) as drugCombination,

    max(a.DispDT, b.DispDT) as startDT format=yymmdd.,

    min(a.RODate, b.RODate) as endDT  format=yymmdd.,

    intck("DAY", calculated startDT, calculated endDT) as nbDays

from

    have as a inner join

    have as b

    on a.PatID=b.PatID and a.Med ne b.Med and

        a.DispDT <= b.DispDT and a.RODate >= b.DispDT;

quit;

PG

[Reeza]

"at the same time" changes the question.

One common approach to this is to create a "medicine cabinet" on a daily basis for each patient.

This is data intensive, but allows you to identify patients on multiple medications as well as the exact overlap dates of interest.

To create the medicine cabinet, create a record for each day of the medication. 

data med_cabinet;

set have;

do date=disp_dt to run_out_date;

output;

end;

run;

[PGStats · Accepted solution]

You can get away with caring only about the change dates (DispDT and RODate) and keeping track of the medication:

data have;

length PatID Med $16;

format DispDT RODate yymmdd.;

input   PatID $ Med DispDT : mmddyy. RODate : mmddyy.;

datalines;

    A           abilify       1/7/2014       4/17/2014

    A           latuda       1/22/2014     5/7/2014

    A           abilify       5/16/2014      6/17/2014

    A           latuda       5/20/2014      10/17/2014

    A           risp           8/1/2014      9/2/2014

    B           clan          2/20/2014     5/2/2014

    B           zipra         2/24/2014    4/24/2014

    B           risp           5/6/2014      6/7/2014

    B           clan           6/10/2014      7/15/2014

;

data changes;

set have;

format chDT yymmdd10.;

ch = 1; chDT = DispDT; output;

ch = -1; chDT = intnx("DAY", RODate, 1); output;

keep PatID Med ch chDT;

run;

proc sort data=changes; by PatID chDT descending ch; run;

data history;

length Meds $100;

array m{20} $16;

format hStart hEnd yymmdd10.;

do until(last.PatID);

    set changes; by PatID;

    if medCount > 0 then do;

        hEnd = intnx("DAY", chDT, -1);

        days = intck("DAY", hStart, hEnd) + 1;

        output;

        end;

    hStart = chDT;

    if ch=1 then m{1} = Med;

    else m{whichc(Med, of m{*})} = "";

    call sortc(of m{*});

    meds = catx(" ", of m{*});

    MedCount = sum(MedCount, ch);

    end;

if medCount > 0 then do;

    call missing(hEnd, days);

    output;

    end;

keep PatID Meds hStart hEnd MedCount days;

run;

proc print dta=history noobs; var PatID hStart hEnd days MedCount Meds; run;

         Pat                                         med

         ID         hStart          hEnd    days    Count    Meds

          A     2014-01-07    2014-01-21     15       1      abilify

          A     2014-01-22    2014-04-17     86       2      abilify latuda

          A     2014-04-18    2014-05-07     20       1      latuda

          A     2014-05-16    2014-05-19      4       1      abilify

          A     2014-05-20    2014-06-17     29       2      abilify latuda

          A     2014-06-18    2014-07-31     44       1      latuda

          A     2014-08-01    2014-09-02     33       2      latuda risp

          A     2014-09-03    2014-10-17     45       1      latuda

          B     2014-02-20    2014-02-23      4       1      clan

          B     2014-02-24    2014-04-24     60       2      clan zipra

          B     2014-04-25    2014-05-02      8       1      clan

          B     2014-05-06    2014-06-07     33       1      risp

          B     2014-06-10    2014-07-15     36       1      clan

Note: I changed your data slightly to remove the overlap in the dates for the drug latuda for patient A. If such date overlaps exist, I wouldn't know how to handle them.

PG

Message was edited by: PG Removed the _TEMPORARY_ option in the array declaration as this implies that the array is retained, which would cause errors in certain circumstances.

[suncawy]

I'm going have to give this a try.  I think what you have is what I'm looking for. This is very helpful and its something, I don't think I could have come up with.

[art297]

Expanding on 's idea, the following appears to produce the file you wanted. However, you didn't select risp for PatID A, although it did overlap with latuda.

data have;

    input (PatID Med) ($)  (Disp_dt Run_Out_date) (: mmddyy10.);

    format Disp_dt Run_Out_date mmddyy10.;

    cards;

    A           abilify       1/7/2014       4/17/2014

    A           latuda       1/22/2014     7/5/2014

    A           abilify       5/16/2014      6/17/2014

    A           latuda       5/20/2014      10/17/2014

    A           risp           8/1/2014      9/2/2014

    B           clan          2/20/2014     5/2/2014

    B           zipra         2/24/2014    4/24/2014

    B           risp           5/6/2014      6/7/2014

    B           clan           6/10/2014      7/15/2014

;

data med_cabinet;

  set have;

  do date=disp_dt to run_out_date;

    output;

  end;

run;

proc sort data=med_cabinet nodupkey;

  by PatID date Med;

run;

proc sql;

  create table want as

    select PatID,Med,Disp_dt,Run_Out_date

      from med_cabinet

        group by patID,date

          having count(distinct med) ge 2

  ;

quit;

proc sort data=want nodupkey;

  by PatID Med Disp_dt Run_Out_date;

run;

[jakarman]

If you are working in sas-datasets you can work using different pointers locations.  Indexes on reference datasets is an option.

Within smaller environments often not worth the effort.

[suncawy]

Everyone thanks for the help.   Now, there's got to be a way to identify it when the dates are overlapping like this, with multiple dispensed dates on the same drug:

Patient     Med         Disp_dt        Run_Out_date

    A           abilify       1/23/2014       3/20/2014

    A           abilify       2/19/2014      4/19/2014

    A           abilify       3/21/2014      5/19/2014

    A           abilify       4/20/2014      6/26/2014

    A           Risp        1/2/2014       2/22/2014

    A           Risp        1/24/2014     4/1/2014

    A           Risp        3/3/2014       4/23/2014

    A           Risp        3/25/2014     5/26/2014

  The next task would be to identify when the patient was on 2 meds at the same time for 90 days or more and if it was less than 90 days, then when was the next time the patient was on 2 days.  So, it seems like I should do some sort of looping, but not sure how.

[PGStats]

What does it mean exactly when the dates overlap for a given drug and patient. Are those simply cases where the patient renewed his/her prescription before being out of the previous supply? - PG

[suncawy]

Actually, after reviewing the data, the solution you provided earlier with tracking the change dates is the right solution.  I looked at the data more closely and appears to solve the issue.   What I did was take the max and min disp_dt by each drug id and applied your solution.  It actually covered all the prescriptions.  Yes, your right.  The patient could have multiple dates where they refilled the same medication, then was started on another medication at the same time.   Then, the patient was taken off the or stopped the med, then started again at a later date.  The solution you provided actually worked once I was able to get the dataset into the max and min disp_dt as I mentioned. It covers all the medications for the patient.