topic Re: Transform dates to weeks and match with other dates? in SAS Programming

Transform dates to weeks and match with other dates?

axl — Wed, 04 Jul 2012 13:46:18 GMT

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

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

Alpay — Wed, 04 Jul 2012 14:18:04 GMT

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

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

axl — Wed, 11 Jul 2012 08:53:44 GMT

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

Kind regards,

Amelie

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

Haikuo — Wed, 11 Jul 2012 12:34:00 GMT

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

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

axl — Wed, 11 Jul 2012 16:00:14 GMT

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

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

Haikuo — Wed, 11 Jul 2012 16:09:30 GMT

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

Regards,

Haikuo

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

axl — Thu, 12 Jul 2012 10:18:29 GMT

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

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

june_bug — Thu, 12 Jul 2012 11:39:18 GMT

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

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

Haikuo — Thu, 12 Jul 2012 12:08:56 GMT

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

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

GeoffreyBrent — Fri, 13 Jul 2012 00:58:18 GMT

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;

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

axl — Fri, 13 Jul 2012 10:30:57 GMT

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!