Hi,
I found a mistake in my first solution. I didn't account for the fact that you have multiple rows of data with the same date.
I took a new approach below, but I don't really like this code much, so hopefully someone else can help. And I haven't fully tested it either. So I wouldn't trust this, but hopefully it helps you think about one way you could maybe do this.
I start with your starting data and transpose it into a long format:
data meds;
input id $ year weeknumber med1 med2 med3 ;
datalines;
001 2008 6 1 0 0
001 2008 8 1 0 0
001 2008 15 1 0 0
001 2016 14 1 0 0
001 2016 14 0 0 1
001 2016 23 1 0 0
001 2016 42 1 0 0
001 2016 42 1 0 0
001 2016 42 1 0 0
001 2016 42 1 0 0
001 2016 42 1 0 0
001 2017 17 0 1 0
001 2017 17 0 0 1
001 2017 17 1 0 0
001 2017 21 1 0 0
001 2017 38 0 0 1
001 2019 1 1 0 0
001 2019 5 1 0 0
001 2019 5 0 1 0
002 2006 7 0 1 0
002 . . 0 0 0
003 2015 50 1 0 0
003 2015 50 0 0 1
003 2016 1 1 0 0
003 2016 4 1 0 0
003 2016 4 0 0 1
004 2006 51 0 0 1
004 2008 3 0 0 1
004 2008 52 0 0 1
004 2009 24 0 0 1
005 2001 13 0 0 1
005 2013 37 0 1 0
005 2014 3 0 1 0
005 2014 8 0 1 0
006 2008 5 1 0 0
006 2008 18 1 0 0
006 2008 20 0 0 1
;
data medslong (keep=id date MedID Med);
set meds(keep=id year weeknumber med1 med2 med3
where=(nmiss(year,weeknumber)=0)
) ;
by id year weeknumber ;
date=input(cats(year,'W',put(weeknumber,z2.)), weekw7.);
array meds{*} med1 med2 med3 ;
do MedID=1 to dim(meds) ;
Med=meds{MedID} ;
output ;
end ;
format date weekw11.;
run ;
Then I find all cases. This should match the results from my earlier solution, but it doesn't because of my mistake in the earlier solution.
*find the Cases. A case means used a medication, if they used no medications for the 24 weeks prior ;
proc sort data=medslong ;
by id date med ;
run ;
data allcases ;
set medslong ;
by id date med;
retain LastMed ;
if first.id then LastMed="01Jan1960"d ;
if Med=1 then do ;
if intck('week',LastMed,date)>=24 then Case=1 ;
else Case=0 ;
if last.date then LastMed=date ;
end ;
else Case=0 ;
drop lastmed ;
run ;
Now find the first case per id for each medication, these are the cases you want:
proc sort data=allcases ;
by id medid descending case date;
run ;
data firstcase ;
set allcases ;
by id medid descending case date;
if first.medid and case=1 ;
run ;
Now read in the cases, and for each case, read through the entire medslong dataset to see if there were any medications taken 52 to 76 weeks before. If there were not, then you have a control record.
data CaseControls (keep=id medid firstcasedate controldate);
set firstcase (keep=id medid date rename=(date=firstcasedate)) ;
do point=1 to nobs ;
set medslong (keep=id date med rename=(id=_id)) nobs=nobs point=point;
if (_id=id) and (med=1) and ( -76 <= intck('week',firstcasedate,date) <= -52) then _HaveMedInWash=1 ;
end ;
if NOT _HaveMedInWash then do ;
ControlDate=intnx('week',firstcasedate,-52) ;
output ;
end ;
format controldate weekw11.;
run ;
when I run that, I get CaseControls similar to what you want:
Med
id firstcasedate ID ControlDate
001 2008-W06-01 1 2007-W06-07
001 2016-W14-01 3 2015-W13-07
002 2006-W07-01 2 2005-W06-07
003 2015-W50-01 1 2014-W49-07
003 2015-W50-01 3 2014-W49-07
004 2006-W51-01 3 2005-W50-07
005 2013-W37-01 2 2012-W37-07
005 2001-W13-01 3 2000-W12-07
006 2008-W05-01 1 2007-W05-07
Again, I don't like my code much, and there may be some off-by-one errors , but I hope the approach might be useful. And hopefully someone else will share better ideas.
I suggest you unmark my first answer as correct.
The Boston Area SAS Users Group is hosting
free webinars!
Next webinar will be in January 2025. Until then, check out our archives:
https://www.basug.org/videos. And be sure to subscribe to our our email list.
