General SAS Programming

[buszhangsy]

I am working on a health-related project and I want to identify a medication use index date for each patient. The medication use index date is defined as the first service date for each patient. In most cases, it's just the first service date regardless of medications. However, the first service date can't be used if there is no additional service for that medication within 6 months. In this case, we have to use the second service date as the medication use index date.

Here is an example.

Patient ID            Medication name       Service date

0000120              Qvar                           08/07/2007

0000120              SPIRIVA                     08/14/2007

0000120              SPIRIVA                     11/14/2007

0000120              SPIRIVA                     01/16/2008

0000120              QVar                           12/05/2008

For this patient, the first service date (08/07/2007) can't be used as the index date because the next service record (12/05/2008) for QVAR is beyond 6 months. I want to use the second service date (08/14/2007) as the index date.

How can I write a SAS code to address this?

Thanks!!

[PGStats · Accepted solution]

OK, This should do it. I expanded your example data for better testing :

data have;

input PatientID MedName $ ServiceDate :mmddyy10.;

MedName = upcase(Medname);

datalines;

0000120              Qvar                           08/07/2007

0000120              SPIRIVA                     08/14/2007

0000120              SPIRIVA                     11/14/2007

0000120              SPIRIVA                     01/16/2008

0000120              QVar                           12/05/2008

0000120              SPIRIVA2                     08/13/2007

0000120              SPIRIVA2                     11/13/2007

0000120              SPIRIVA2                     01/15/2008

0000120              SPIRIVA3                     08/15/2007

0000120              SPIRIVA3                     11/15/2007

0000120              SPIRIVA3                     01/17/2008

0000120              SPIRIVA4                     01/11/2008

;

proc sort data=have; by PatientID MedName ServiceDate; run;

data temp / view=temp;

if not skip then

    set have(keep=ServiceDate rename=ServiceDate=NextDate firstObs=2) end=skip;

set have; by PatientID MedName;

if last.medName then call missing (NextDate);

run;

data temp2;

do until(last.MedName);

    set temp; by PatientID MedName;

    if missing(indexDate) then

        if not missing(NextDate) then

            if intck("MONTH", ServiceDate, NextDate, 'C') < 6 then

                indexDate = ServiceDate;

    end;

if not missing(indexDate);

drop ServiceDate NextDate;

format indexDate mmddyy10.;

run;

proc sort data=temp2; by patientID indexDate; run;

data want;

set temp2; by PatientID;

if first.PatientID;

run;

proc print data=want noobs; run;

PG

[DBailey]

how about something like this?

proc sql;

create table want as

select

     patient_id,

     medication_name,

     min(Service_Date) as Medication_Use_Index_Date

from

     have t1

where exists (select * from want where patient_id=t1.patient_id and medication_name=t1.medication_name and service_date > t1.service_date and intck('month',t1.service_date,service_date)<=6);

quit;

[buszhangsy]

Thanks! I only learned little SQL language. What does "from have t1" do in the program? I submitted the program and saw error in the log: file work.have.data do not exist, file work.want.data do not exist..

[ballardw]

The convention on this forum is to use "have" for the name of the dataset that you have to start with and "want" is the desired output. That saves us trying to spend lots of time with convoluted library/dataset name combinations.

So where you see "have" replace with your library and dataset.

[buszhangsy]

Still got an error in the log: File WORK.WANT.DATA does not exist....

[ballardw]

Post the code you ran along with the full log result.

[buszhangsy]

CODE:

proc sql;

  create table index2 as

  select

    baseid,

    FDB_GNN,

    min(filldate) as indexdate

  from

    index1 t1

    where exists(select * from index2 where baseid=t1.baseid and FDB_GNN=t1.FDB_GNN and filldate>t1.filldate

               and intck('month',t1.filldate,filldate)<=6);

quit;

LOG:

444  proc sql;

445    create table index2 as

446    select

447      baseid,

448      FDB_GNN,

449      min(filldate) as indexdate

450    from

451      index1 t1

452      where exists(select * from index2 where baseid=t1.baseid and FDB_GNN=t1.FDB_GNN and filldate>t1.filldate

453                 and intck('month',t1.filldate,filldate)<=6);

ERROR: File WORK.INDEX2.DATA does not exist.

454  quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.07 seconds

      cpu time            0.00 seconds

[PGStats]

For a dataset (/view) solution:

data have;

input PatientID MedName $ ServiceDate :mmddyy10.;

MedName = upcase(Medname);

datalines;

0000120              Qvar                           08/07/2007

0000120              SPIRIVA                     08/14/2007

0000120              SPIRIVA                     11/14/2007

0000120              SPIRIVA                     01/16/2008

0000120              QVar                           12/05/2008

;

proc sort data=have; by PatientID MedName ServiceDate; run;

data temp / view=temp;

if not skip then

    set have(keep=ServiceDate rename=ServiceDate=NextDate firstObs=2) end=skip;

set have; by PatientID MedName;

if last.medName then call missing (NextDate);

run;

data want;

do until(last.MedName);

    set temp; by PatientID MedName;

    if missing(indexDate) then

        if missing(NextDate) or intck("MONTH", ServiceDate, NextDate, 'C') < 6 then

            indexDate = ServiceDate;

    end;

drop ServiceDate NextDate;

format indexDate mmddyy10.;

run;

proc print data=want noobs; run;

PG

[buszhangsy]

For most patients there were 2 index date created....I believe one of them was correct, but not sure how the other one generated.....

[PGStats]

I assumed you wanted one index date for each medication name, for each patientID. Is that right?

[buszhangsy]

No, I only need one index date. In most cases, that index date should be the first service date regardless of medications. However, if the corresponding medication does not have a follow-up service within 6 months, its service date can not be used as index date. I need to examine the second service date and see if it meets the criteria.

Thanks!

[PGStats]

Another detail. In the absence of a proper index date for a given patient, do you want missing values, or the last date as the index, or to omit that patientID ?

[buszhangsy]

Omit the patient if we can not find a proper index date. Having an index date is one of the inclusion criteria for the study sample.

Thanks!!

[PGStats · Accepted solution]

OK, This should do it. I expanded your example data for better testing :

data have;

input PatientID MedName $ ServiceDate :mmddyy10.;

MedName = upcase(Medname);

datalines;

0000120              Qvar                           08/07/2007

0000120              SPIRIVA                     08/14/2007

0000120              SPIRIVA                     11/14/2007

0000120              SPIRIVA                     01/16/2008

0000120              QVar                           12/05/2008

0000120              SPIRIVA2                     08/13/2007

0000120              SPIRIVA2                     11/13/2007

0000120              SPIRIVA2                     01/15/2008

0000120              SPIRIVA3                     08/15/2007

0000120              SPIRIVA3                     11/15/2007

0000120              SPIRIVA3                     01/17/2008

0000120              SPIRIVA4                     01/11/2008

;

proc sort data=have; by PatientID MedName ServiceDate; run;

data temp / view=temp;

if not skip then

    set have(keep=ServiceDate rename=ServiceDate=NextDate firstObs=2) end=skip;

set have; by PatientID MedName;

if last.medName then call missing (NextDate);

run;

data temp2;

do until(last.MedName);

    set temp; by PatientID MedName;

    if missing(indexDate) then

        if not missing(NextDate) then

            if intck("MONTH", ServiceDate, NextDate, 'C') < 6 then

                indexDate = ServiceDate;

    end;

if not missing(indexDate);

drop ServiceDate NextDate;

format indexDate mmddyy10.;

run;

proc sort data=temp2; by patientID indexDate; run;

data want;

set temp2; by PatientID;

if first.PatientID;

run;

proc print data=want noobs; run;

PG

[buszhangsy]

Thank you PG! I will run it first thing tomorrow morning!! The data can only be accessed through my office desktop computers.

I will let you know the result!