BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
buszhangsy
Calcite | Level 5

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!!

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

PG

View solution in original post

16 REPLIES 16
DBailey
Lapis Lazuli | Level 10

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5

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

ballardw
Super User

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

buszhangsy
Calcite | Level 5

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
Opal | Level 21

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

PG
buszhangsy
Calcite | Level 5

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
Opal | Level 21

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

PG
buszhangsy
Calcite | Level 5

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
Opal | Level 21

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 ?

PG
buszhangsy
Calcite | Level 5

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
Opal | Level 21

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

PG
buszhangsy
Calcite | Level 5

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 16 replies
  • 1308 views
  • 6 likes
  • 5 in conversation