09-29-2014 01:52 PM
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!!
09-29-2014 09:59 PM
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
09-29-2014 02:17 PM
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;
09-29-2014 03:51 PM
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..
09-29-2014 04:00 PM
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.
09-29-2014 04:07 PM
Still got an error in the log: File WORK.WANT.DATA does not exist....
09-29-2014 04:24 PM
Post the code you ran along with the full log result.
09-29-2014 05:34 PM
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
09-29-2014 04:28 PM
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
09-29-2014 05:47 PM
For most patients there were 2 index date created....I believe one of them was correct, but not sure how the other one generated.....
09-29-2014 08:09 PM
I assumed you wanted one index date for each medication name, for each patientID. Is that right?
09-29-2014 08:16 PM
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!
09-29-2014 08:28 PM
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 ?
09-29-2014 09:10 PM
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!!
09-29-2014 09:59 PM
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
09-29-2014 10:08 PM
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!
Need further help from the community? Please ask a new question.