## Transform dates to weeks and match with other dates?

Occasional Contributor
Posts: 5

# Transform dates to weeks and match with other dates?

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

Frequent Contributor
Posts: 95

## Re: Transform dates to weeks and match with other dates?

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;

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;

output out=study_sum(drop=_

sum=;

run;

Occasional Contributor
Posts: 5

## Re: Transform dates to weeks and match with other dates?

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

Kind regards,

Amelie

Posts: 3,167

## Re: Transform dates to weeks and match with other dates?

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

Occasional Contributor
Posts: 5

## Re: Transform dates to weeks and match with other dates?

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:

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

Posts: 3,167

## Re: Transform dates to weeks and match with other dates?

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

Regards,

Haikuo

Occasional Contributor
Posts: 5

## Re: Transform dates to weeks and match with other dates?

 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

Occasional Contributor
Posts: 19

## Re: Transform dates to weeks and match with other dates?

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

Posts: 3,167

## Re: Transform dates to weeks and match with other dates?

Here is something hopefully can get you started:

data have;

infile cards truncover;

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 weekno weekno1-weekno6;

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

Contributor
Posts: 30

## Re: Transform dates to weeks and match with other dates?

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;

Occasional Contributor
Posts: 5