I want to bring my data in a case-crossover format where cases serve as their own controls. However, I need to clean the data first. In the data below, using the original medication purchase variables med1-med3, a medication purchase as a case was identified. The criteria for a medication purchase to be valid as a case was to fulfill a requirement of 24 week washout period i.e a purchase is considered a case if a person has not bought any type of medication 24 weeks before the current purchase. For example for med1 to be a case no other type of medication should be bought 24 weeks before the purchase and if there is a purchase in either of the medication types then it is not a case. However, after fulfilling the washout requirement if different types of medications are bought in the same week then it should be considered a case for each type. Medication purchases have been identified as a case and are recorded in variables casemed1, casemed2, and casemed3. Please note, there is no date variable in the original data due to privacy issues. The date variables was created using sas code (date=input(cats(year,'W',put(weeknumber,z2.)), weekw7.)).
data have;
input id $ year weeknumber med1 med2 med3 casemed1 casemed2 casemed3 date;
datalines;
001 2008 6 1 0 0 1 0 0 2008-W06-01
001 2008 8 1 0 0 0 0 0 2008-W08-01
001 2008 15 1 0 0 0 0 0 2008-W15-01
001 2016 14 1 0 0 1 0 0 2016-W14-01
001 2016 14 0 0 1 0 0 0 2016-W14-01
001 2016 23 1 0 0 0 0 0 2016-W23-01
001 2016 42 1 0 0 0 0 0 2016-W42-01
001 2016 42 1 0 0 0 0 0 2016-W42-01
001 2016 42 1 0 0 0 0 0 2016-W42-01
001 2016 42 1 0 0 0 0 0 2016-W42-01
001 2016 42 1 0 0 0 0 0 2016-W42-01
001 2017 17 0 1 0 0 1 0 2017-W17-01
001 2017 17 0 0 1 0 0 0 2017-W17-01
001 2017 17 1 0 0 0 0 0 2017-W17-01
001 2017 21 1 0 0 0 0 0 2017-W21-01
001 2017 38 0 0 1 0 0 0 2017-W38-01
001 2019 1 1 0 0 1 0 0 2019-W01-01
001 2019 5 1 0 0 0 0 0 2019-W05-01
001 2019 5 0 1 0 0 0 0 2019-W01-01
002 2006 7 0 1 0 0 1 0 2006-W07-01
003 2015 50 1 0 0 1 0 0 2015-W50-01
003 2015 50 0 0 1 0 0 0 2015-W50-01
003 2016 1 1 0 0 0 0 0 2016-W01-01
003 2016 4 1 0 0 0 0 0 2016-W04-01
003 2016 4 0 0 1 0 0 0 2016-W04-01
004 2006 51 0 0 1 0 0 1 2006-W51-01
004 2008 3 0 0 1 0 0 1 2008-W03-01
004 2008 52 0 0 1 0 0 1 2008-W52-01
004 2009 24 0 0 1 0 0 1 2009-W24-01
005 2001 13 0 0 1 0 0 1 2001-W13-01
005 2013 37 0 1 0 0 1 0 2013-W37-01
005 2014 3 0 1 0 0 0 0 2014-W03-01
005 2014 8 0 1 0 0 0 0 2014-W08-01
006 2008 5 1 0 0 1 0 0 2008-W05-01
006 2008 18 1 0 0 0 0 0 2008-W18-01
006 2008 20 0 0 1 0 0 0 2008-W20-01
;
In the next step, I need to identify first medication purchase per person. This should be separate for each medication type. Variables that should be used to identify first purchase for each type separately are casemed1, casemed2, and casemed3. This first purchase of each medication type is the final case.
In the final step, I need to select a control for each case. Control is the same week as the first purchase week but one year earlier. To be clear, id 001's first purchase of med1 was in week 6 year 2008. Control for this is id 001 in week 6 in the year 2007. The tricky part here is 24 weeks washout period. Washout period of 24 weeks also applies on controls. if there was a purchase in the control week or 24 weeks before the control week of any medication type (med1 or med2 or med3) than the control is invalid and so is the case.
For a case (first purchase as case) to be valid there should not be any medication type bought in the control week or 24 weeks before.
The final data (supposed, not extracted from the above) should somewhat look like;
data final;
input id $ year weeknumber case firt_purchasemed1 firt_purchasemed2 firt_purchasemed3 ;
datalines;
001 2008 6 1 1 0 0
001 2007 6 0 1 0 0
001 2017 17 1 0 1 0
001 2016 17 0 0 1 0
002 2006 7 1 0 1 0
002 2005 7 0 0 1 0
004 2006 51 1 0 0 1
004 2006 51 0 0 0 1
005 2001 13 1 0 0 1
005 2000 13 0 0 0 1
005 2013 37 1 0 1 0
005 2012 37 0 0 1 0
006 2008 5 1 1 0 0
006 2007 5 0 1 0 0
run;
I need help in writing a SAS code to achieve this. I have spent a lot of time on this but couldn't figure out.
I am happy to answer if something is not clear.
Thank you!
