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!!
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
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;
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..
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.
Still got an error in the log: File WORK.WANT.DATA does not exist....
Post the code you ran along with the full log result.
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
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
For most patients there were 2 index date created....I believe one of them was correct, but not sure how the other one generated.....
I assumed you wanted one index date for each medication name, for each patientID. Is that right?
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!
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 ?
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!!
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
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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: