Assigning Drug Regimen

Reply
Contributor
Posts: 24

Assigning Drug Regimen

Hello there,

I am working on this project that is drug centric.

I have the following data for drug from a claim for a patient. I am supposed to assign DRUG REGIMEN based on the following:

Rules:

1. The Drugs A,B,C and D should have been administered in a span 3 days from the First Fill Date.

2. The Prescriber Patient relation has to be maintained.

3. All 4 drugs should have been administered in order to be tagged with a drug regimen.

Data:

PRESCRIBERIDPATIENTIDFILLDATEDRUGNAME
ABC101Jan2011A
ABC102Jan2011B
ABC102Jan2011C
ABC103Jan2011D
ABC107Jan2011A

Output Expected:

PRESCRIBERIDPATIENTIDREGIMEN
ABC1XXX
ABC1OTHER

As you can see, the first 4 records in the input data are spanned in 3 days and hence a regimen XXX is assigned and only 1 record was written out.

Any help would be highly appreciated.

Respected Advisor
Posts: 4,644

Re: Assigning Drug Regimen

Here is one way to do this:

data have;

input PRESCRIBERID $ PATIENTID FILLDATE :date9. DRUGNAME $;

format FILLDATE date9.;

datalines;

ABC 1 01Jan2011 A

ABC 1 02Jan2011 B

ABC 1 02Jan2011 C

ABC 1 03Jan2011 D

ABC 1 07Jan2011 A

;

proc sql;

create table regimenABCD as

select hA.PRESCRIBERID, hA.PATIENTID, hA.FILLDATE as FILLDATEA,

     hB.FILLDATE as FILLDATEB, hC.FILLDATE as FILLDATEC, hD.FILLDATE as FILLDATED

from

      have as hA inner join

      have as hB

           on hA.PRESCRIBERID=hB.PRESCRIBERID and

               hA.PATIENTID=hB.PATIENTID and

               hB.FILLDATE>=hA.FILLDATE inner join

      have as hC

          on hA.PRESCRIBERID=hC.PRESCRIBERID and

               hA.PATIENTID=hC.PATIENTID and

               hC.FILLDATE>=hB.FILLDATE inner join

     have as hD

          on hA.PRESCRIBERID=hD.PRESCRIBERID and

               hA.PATIENTID=hD.PATIENTID and

               hD.FILLDATE between hC.FILLDATE and intnx("DAY", hA.FILLDATE, 2)

where hA.DRUGNAME = "A" and hB.DRUGNAME = "B" and hC.DRUGNAME = "C" and hD.DRUGNAME = "D";

 

select * from regimenABCD;

create table notRegimenABCD as

select h.* from have as h where h.DRUGNAME="A" and not exists

      (select * from regimenABCD

      where

      PRESCRIBERID=h.PRESCRIBERID and

      PATIENTID=h.PATIENTID and

      FILLDATEA=h.FILLDATE)

union

select h.* from have as h where h.DRUGNAME="B" and not exists

      (select * from regimenABCD

      where

      PRESCRIBERID=h.PRESCRIBERID and

      PATIENTID=h.PATIENTID and

      FILLDATEB=h.FILLDATE)

union

select h.* from have as h where h.DRUGNAME="C" and not exists

      (select * from regimenABCD

      where

      PRESCRIBERID=h.PRESCRIBERID and

      PATIENTID=h.PATIENTID and

      FILLDATEC=h.FILLDATE)

union

select h.* from have as h where h.DRUGNAME="D" and not exists

      (select * from regimenABCD

      where

      PRESCRIBERID=h.PRESCRIBERID and

      PATIENTID=h.PATIENTID and

      FILLDATED=h.FILLDATE);

select * from notRegimenABCD;

create table want as

select PRESCRIBERID, PATIENTID, FILLDATEA as FILLDATE format=date9.,

     "ABCD" as REGIMEN from REGIMENABCD

union all

select PRESCRIBERID, PATIENTID, FILLDATE,

     "OTHER" as REGIMEN from notRegimenABCD

order by PRESCRIBERID, PATIENTID, FILLDATE, REGIMEN;

select * from want;

quit;

PG

PG
Contributor
Posts: 24

Re: Assigning Drug Regimen

Thanks for the quick turn around PG Stats. I tested this code to see if I can achieve the required result and failed.

When I run this code over a bigger sample, the result is not as expected. I'll try to tweak this if possible.

Respected Advisor
Posts: 4,644

Re: Assigning Drug Regimen

Was your adaptation a simple name replacement job or was it more complicated? Look at tables regimenABCD and notRegimenABCD (or their equivalent in your version of the code), they should help you pinpoint what went wrong.

PG

PG
Contributor
Posts: 24

Re: Assigning Drug Regimen

The problem ins more complicated. This code is working for the sample I had given. I got a much bigger sample now and the same code is not returning the desired results.

The requirement is to assign the drug regimen for a set of pharmacy claims based on the FILL_DATE. There is a specific set of 4 drugs that should have been prescribed by a prescriber during a span of 3 days to a patient. If all the drugs were prescribed, then one record should be written out. If for some reason, only 3 drugs are prescribed in 3 days and on the 5 day the 4th drug is given, the all 4 records should be tagged as OTHERS and retained(this is an assumption that I am making although I feel that this does not make sense). All this should be done based on the FILL DATE from the claim.

Here is some more data that might be helpful.

INPUT
PRESCRIBERIDPATIENTIDFILLDATEDRUGNAME
ABC107-Feb-11D
ABC116-Mar-11D
ABC117-Mar-11A
ABC117-Mar-11B
ABC117-Mar-11C
ABC104-Apr-11A
ABC104-Apr-11B
ABC106-Mar-11C
ABC119-Apr-11D
ABC120-Apr-11A
ABC120-Apr-11B
ABC125-Apr-11C
ABC106-May-11A
ABC106-May-11B
ABC109-May-11D
ABC111-May-11C
ABC125-May-11A
ABC125-May-11B

This is what we have. There are still some clarifications that I have to get from the client. Firstly, what happens to claims records that do not fall in a Regimen. Secondly, are we to write all such records as is or should those be consolidated as 1 records (OTHER regimen); consider records with fill date between 04-APR2011 and 25-APR-2011. Since none of the 4 drugs fall in the 3 day criteria, I am assuming that only one record should be output for that case as OTHER regimen for that month (though the clarification is still pending from the client).

The below is output that is expected (again this is just an assumption). Per my explanation above, for 201104 and 201105, it may be 1 record only (but you never know what the client will ask). The case for MAY 2011 is similar to that of APR 2011.

FILLMONTHPRESCRIBERIDPATIENTIDFILLDATEREGIMEN
201102ABC107-Feb-11OTHER
201103ABC116-Mar-11XXX
201104ABC104-Apr-11OTHER
201105ABC106-May-11OTHER
Ask a Question
Discussion stats
  • 4 replies
  • 332 views
  • 0 likes
  • 2 in conversation