Transform dates to weeks and match with other dates?

[axl]

Hi!

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:

Individual 1

Medication week 1: 0 adverse effect.

Medication week 2: 0 adverse effects.

Medication week 3: 1 adverse effects.

Medication week 4: 1 adverse effects.

Individual 2

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?

Thanks!

Kind regards,

Amelie

[Alpay]

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.

data have;

  input indv meddate anydtdte. adveff;

  format meddate mmddyys10.;

datalines;

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

;

run;

proc sort data=have;

     by indv meddate;

run;

data want;

    set have;

    by indv;

    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 */

    drop firstmedweek;

run;

proc summary data=want nway;

  class indv medweekcnt;

  var adveff;

  output out=study_sum(drop=_:)

  sum=;

run;

[axl]

Thank you so much for the answer! I will try this with my data.

Kind regards,

Amelie

[Haikuo]

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.

Regards,

Haikuo

[axl]

Hello again,

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:

Thanks!

Kind regards,

Amelie

[Haikuo]

Let 's stop guessing. Please post some faked sample data. A description, no matter how detailed, still could be ambiguous.

Regards,

Haikuo

[axl]

ID	treatm_start	treatm_end	adv_eff1	adv_eff2	adv_eff3	adv_eff4	adv_eff5	adv_eff6
1	2008-04-09	2010-20-20	2008-04-11	2008-04-11	2008-04-13	2008-04-17	2008-04-30	2008-05-01
2	2009-11-16	2009-12-24	2009-12-16	2009-12-17	2009-12-18	.	.	.
3	2005-08-17	2010-12-28	.	.	.	.	.	.
4	2006-12-08	2008-01-09	2006-12-15	.	.	.	.	.
5	2008-02-20	2010-11-26	2008-02-28	2008-03-01	2008-03-10	2008-03-12	2008-03-14	2008-03-14
6	2006-12-05	2007-09-26	.	.	.	.	.	.
7	2008-04-02	2008-04-02	2008-04-02	2008-04-02	.	.	.	.
8	2006-10-24	2007-03-13	2006-10-28	2006-10-29	2006-11-02	.	.	.
9	2006-02-06	2007-05-03	2006-02-10	2006-02-10	2006-02-10	2006-02-13	2006-02-17	.
10	2007-12-27	2008-03-24	2007-12-27	.	.	.	.	.

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.

Best regards,

Amelie

[june_bug]

Tx Amelie

But I need to identify the specific day like a Friday is the 6 day so it should read:

1= Sunday

2= Monday

3=Tuesday, etc.

Any chance you could help with this?

Jbug

[Haikuo]

Here is something hopefully can get you started:

data have;

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.;

cards;

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    .    .    .    .    .

;

data have;

set have;

array adv adv_eff1-adv_eff6;

array weekno weekno1-weekno6;

do i=1 to dim(adv);

   weekno(i)=ceil((adv(i)-treatm_start+1)/7);

end;

run;

proc transpose data=have out=have1(rename=col1=weekno drop=_name_);

by id;

var week: ;

run;

proc freq data=have1;

table id*weekno /out=want (drop=percent where=(not missing (weekno)));

run;

proc print;run;

Regards,

Haikuo

[GeoffreyBrent]

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:

data allweeks;

set have (keep=id tretm_start treatm_end);

maxweeks=ceil((treatm_end-treatm_start+1)/7);

do weekno=1 to maxweeks;

output;

end;

keep id weekno;

run;

proc sort data=allweeks;

by id weekno;

quit;

proc sort data=want;

by id weekno;

quit;

/* I don't think either of these PROC SORTS are actually necessary but I always like to confirm sort before merging */

data want;

merge allweeks want;

by id weekno;

if missing(count) then count=0;

run;

[axl]

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!

/A