I have a database of subjects, medications that were prescribed, and the dates they were prescribed. I would like to assign medication regimens to each subject by looking at which of these medications were used over the same time period. A subject can have more than one regimen; what determines the regimen are the types of medications and when they were being taken (Script_EnteredDate = date the medication was prescribed).
I was able to combine rows that had the same dates, look at their medication combinations, and assign regimens accordingly (my code for this is very long, so I have put it at the very bottom of this post for reference). The problem is that a lot of regimens are still missing.
Example of the data is below:
Obs Script_EnteredDate SUBJECTID sum_INT sum_nNRTI sum_PI sum_Ritonavir sum_Other sum_NRTI regimen
1 07/10/2007 454 0 0 2 0 0 1 .
2 04/05/2008 454 0 0 2 0 0 1 .
3 07/25/2012 455 0 1 0 0 0 1 .
4 12/12/2006 455 0 1 0 0 0 0 .
5 01/26/2007 455 0 1 0 0 0 0 .
6 01/27/2007 455 0 0 0 0 0 1 NRTI-based
7 05/06/2010 455 0 0 0 0 0 3 NRTI-based
8 05/12/2010 455 0 1 0 0 0 1 .
9 05/13/2010 455 0 0 0 0 0 1 NRTI-based
10 08/04/2010 460 0 1 0 0 0 0 .
11 08/11/2010 460 0 1 0 0 0 1 .
12 08/12/2010 460 0 0 0 0 0 1 NRTI-based
13 01/10/2007 470 0 0 1 0 0 0 .
14 10/11/2007 470 0 0 1 0 0 0 .
15 10/16/2007 471 0 0 1 0 0 1 .
16 10/26/2007 471 0 0 2 0 0 1 .
17 05/28/2008 471 0 0 2 0 0 1 .
18 11/28/2006 472 0 0 1 0 0 0 .
19 11/29/2006 472 0 0 0 0 0 1 NRTI-based
It is likely that there are a few subjects whose medication combination does not fit one of my regimen criteria. HOWEVER, my theory is that in some cases, the medications were prescribed close together but not necessarily on the same day. This would mean that there are a lot of single medications that are not being assigned to a regimen (because my code was written to only count medications as part of a row/regimen if they were prescribed on exactly the same day), even though those medications were taken during the same time period (give or take a few weeks, or sometimes months). I would like to be able to combine (specifically, sum) some of the rows together if their dates are close to each other, for example only two weeks apart. For example, the last two observations (subjectid 472) would below in the same row because their prescription dates are only one day apart:
18 11/28/2006 472 0 0 1 0 0 0 .
19 11/29/2006 472 0 0 0 0 0 1 NRTI-based
I would like these two rows to turn into something like this:
18 11/29/2006 472 0 0 1 0 0 1 NRTI-based*
(*Note: This "NRTI-based" regimen assignment could change to another regimen depending on what medications end up being combined into this row. But I already have a code to do this assignment for each row, assuming the rows are produced correctly.)
Any help would be much appreciated! Thank you in advance.
CODE I USED PREVIOUSLY TO COMBINE ROWS WITH SAME DATES:
proc sql;
create table WANT1 as select
Script_EnteredDate,
SUBJECTID,
sum(INT) as sum_INT
from clean.pharm_arv
group by SUBJECTID, Script_EnteredDate;
quit;
proc sql;
create table WANT2 as select
Script_EnteredDate,
SUBJECTID,
sum(mNRTI) as sum_mNRTI
from clean.pharm_arv
group by SUBJECTID, Script_EnteredDate;
quit;
proc sql;
create table WANT3 as select
Script_EnteredDate,
SUBJECTID,
sum(NRTI) as sum_NRTI
from clean.pharm_arv
group by SUBJECTID, Script_EnteredDate;
quit;
proc sql;
create table WANT4 as select
Script_EnteredDate,
SUBJECTID,
sum(nNRTI) as sum_nNRTI
from clean.pharm_arv
group by SUBJECTID, Script_EnteredDate;
quit;
proc sql;
create table WANT5 as select
Script_EnteredDate,
SUBJECTID,
sum(PI) as sum_PI
from clean.pharm_arv
group by SUBJECTID, Script_EnteredDate;
quit;
proc sql;
create table WANT6 as select
Script_EnteredDate,
SUBJECTID,
sum(Ritonavir) as sum_Ritonavir
from clean.pharm_arv
group by SUBJECTID, Script_EnteredDate;
quit;
proc sql;
create table WANT7 as select
Script_EnteredDate,
SUBJECTID,
sum(Other) as sum_Other
from clean.pharm_arv
group by SUBJECTID, Script_EnteredDate;
quit;
DATA clean.pharm_ARVSUM;
MERGE WANT1 WANT2 WANT3 WANT4 WANT5 WANT6 WANT7;
BY SUBJECTID SCRIPT_ENTEREDDATE;
RUN;
