07-04-2012 09:46 AM
I'm new to SAS and I'm struggling with a descriptive analysis that requires some knowledge about SAS dates. I hope someone here can help me:
I have a dataset with individuals that are on medications during different time periods. They have also reported the adverse effects that they experience.
I want to study the number of adverse effects reported for each week on medication.
For each individual I have a medication period (e.g. 2001-03-01 to 2001-03-28). The actual dates for the medication period varies between individuals.
I also have variables that contain each adverse effect and it's date (e.g. 2001-03-10, one adverse effect; 2001-03-21; one adverse effect; 2001-03-27; one adverse effect etc.). If there are two adverse effects on the same day, they are reported separately on two different rows.
1. What I would like to do is transform each individual's medication period into medication weeks starting from week 1, (i.e. the first week on medication would be week no. 1, the second week on medication would be week no. 2, the third week on medication would be week no. 3 etc).
2. Then I would like to match each reported adverse effect to it's respective week. So that I, for each individual, would get something like this:
Medication week 1: 0 adverse effect.
Medication week 2: 0 adverse effects.
Medication week 3: 1 adverse effects.
Medication week 4: 1 adverse effects.
Medication week 1: 3 adverse effect.
Medication week 2: 6 adverse effects.
Medication week 3: 5 adverse effects.
Medication week 4: 0 adverse effects.
Medication week 5: 0 adverse effects.
Medication week 6: 0 adverse effects.
And so on for each individual.
So, how do I convert the medication periods into weeks and how do I match the dates for the adverse effects with these weeks?
07-04-2012 10:18 AM
After sorting your data by indvidual and medication date find the first day of the week for the first medication date for a given individual.
After that, count the number of weeks since this date for all medication dates and add 1 to the result so that first week gets a value of 1 instead of 0.
input indv meddate anydtdte. adveff;
format meddate mmddyys10.;
1 01012012 0
1 01042012 1
1 01072012 0
1 01102012 0
1 01142012 0
1 01202012 1
1 01262012 0
2 03022012 0
2 03042012 0
2 03082012 1
2 03122012 0
2 03172012 1
2 03172012 1
2 03222012 1
2 03272012 0
proc sort data=have;
by indv meddate;
if first.indv then firstmedweek = INTNX('Week',Meddate,0,'beg'); /* the beginning of the week for the first medication date for a given individual */
retain firstmedweek; /* retain the value for firstmedweek for other observations */
medweekcnt = INTCK('Week',firstmedweek,Meddate) + 1; /* count the number of weeks since firstmedweek for the same individual */
proc summary data=want nway;
class indv medweekcnt;
07-11-2012 08:34 AM
Alpay has pointed you to the right direction in general. While if you are doing something clinical trial related, here is my 2 cents:
1. Using 'beginning' of the week to mark the onset of the treatment week may not be what you want. I would choose the real treat start date as the start date, and count 7-days intervals on top of that. Drug adversary effect is more relevant to the actually length of time being administrated. Applying to Alpay's code, using 'same' operator instead of 'beg'. Or simply use ceil((date_whatever-date_start+1)/7) to come up with the week no.
2. From my personal preference: if you are just doing the count, such as this scenario, using proc freq would be more efficient.
07-11-2012 12:00 PM
thank you for your advice! I started to do this but couldn't follow through as I don't have each medication date on a separate row. I think that I failed to describe my data set properly;
For medication date, I have one variable with the start date of the medication, and another variable with the end date of the medication. I also have a separate variable for each adverse effect (which is the date of the adverse effect).
For each individual I have the following:
individual1 treatm_start_date treatm_end_date adverse_effect_date1 adverse_effect_date2 adverse_effect_date3 adverse_effect_date4 etc.
So, to follow your example, I guess I would have to transform the treatment start date and the treatment end date variables somehow...?
Sorry for all the questions but I'm new to SAS so this is really challenging to me :smileyconfused:
07-12-2012 06:18 AM
Ok, so here's some faked sample data showing how my dataset looks like (the dots indicate that there are no adverse effects reported). I hope that this clarifies things, otherwise let me know.
07-12-2012 07:39 AM
But I need to identify the specific day like a Friday is the 6 day so it should read:
Any chance you could help with this?
07-12-2012 08:08 AM
Here is something hopefully can get you started:
infile cards truncover;
input ID (treatm_start treatm_end adv_eff1 adv_eff2 adv_eff3 adv_eff4 adv_eff5 adv_eff6) (:mmddyy10.);
format treatm_start treatm_end adv_eff1 adv_eff2 adv_eff3 adv_eff4 adv_eff5 adv_eff6 mmddyy10.;
1 4/9/2008 12/20/2008 4/11/2008 4/11/2008 4/13/2008 4/17/2008 4/30/2008 5/1/2008
2 11/16/2009 12/24/2009 12/16/2009 12/17/2009 12/18/2009 . . .
3 8/17/2005 12/28/2010 . . . . . .
4 12/8/2006 1/9/2008 12/15/2006 . . . . .
5 2/20/2008 11/26/2010 2/28/2008 3/1/2008 3/10/2008 3/12/2008 3/14/2008 3/14/2008
6 12/5/2006 9/26/2007 . . . . . .
7 4/2/2008 4/2/2008 4/2/2008 4/2/2008 . . . .
8 10/24/2006 3/13/2007 10/28/2006 10/29/2006 11/2/2006 . . .
9 2/6/2006 5/3/2007 2/10/2006 2/10/2006 2/10/2006 2/13/2006 2/17/2006 .
10 12/27/2007 3/24/2008 12/27/2007 . . . . .
array adv adv_eff1-adv_eff6;
array weekno weekno1-weekno6;
do i=1 to dim(adv);
proc transpose data=have out=have1(rename=col1=weekno drop=_name_);
var week: ;
proc freq data=have1;
table id*weekno /out=want (drop=percent where=(not missing (weekno)));
07-12-2012 08:58 PM
It looks like the OP wants to include weeks with zero adverse events. This can be done with a minor addition to Haikuo's code:
set have (keep=id tretm_start treatm_end);
do weekno=1 to maxweeks;
keep id weekno;
proc sort data=allweeks;
by id weekno;
proc sort data=want;
by id weekno;
/* I don't think either of these PROC SORTS are actually necessary but I always like to confirm sort before merging */
merge allweeks want;
by id weekno;
if missing(count) then count=0;
07-13-2012 06:30 AM
Thank you so much, Geoffrey, Haikuo and Jbug for your helpful advice. I will try this when I get back to work and see how it goes. Again, thanks a lot!