How to calculate the relative month for patient recruitment period (a new continuous variable)

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

How to calculate the relative month for patient recruitment period (a new continuous variable)

Hi,

 

We recruited patients from 02/07/2014 to 04/20/2017 (please see admission date). I would like to call the Febuary 2014 as "Month 1", March 2014 as "Month 2", April 2014 as "Month 3", May 2015 as "Month 4"...etc. How do I create a new continuous variable named "Month" and simply list the 1, 2, 3, 4 for the corresponding recruitment date? Thank you very much!

 

The first column is patient ID and the 2nd column is the recuitment date:

 

01-0012014-02-07
01-0022014-02-21
01-0032014-03-28
01-0042014-03-25
01-0052014-03-25
01-0062014-03-28
01-0072014-04-22
01-0082014-05-27
01-0092014-05-28
01-0102014-06-20
01-0112014-07-01
01-0122014-07-24
01-0132014-08-12
01-0142014-09-26
01-0152014-10-02
01-0162014-11-06
01-0172014-11-18
01-0182014-10-31
01-0192014-12-05
01-0202014-12-22
01-0282015-03-16
01-0542014-10-02
01-0602014-07-03
02-0012014-03-20
02-0022014-04-01
02-0042014-05-22
02-0052014-07-17
02-0062014-04-03
02-0072014-07-22
02-0082014-09-18
02-0092014-09-11
02-0102014-09-25
02-0112014-10-28
02-0122014-11-06
02-0132015-01-15
01-0222015-02-09
01-0232015-01-26
01-0262015-03-16
01-0272015-03-20
01-0292015-03-25
01-0302015-04-27
01-0312015-05-01
01-0322015-05-06
01-0332015-05-11
01-0352015-06-12
01-0362015-06-18
01-0372015-06-17
01-0382015-07-20
01-0392015-07-31
01-0402015-08-12
01-0412015-09-10
01-0422015-08-18
01-0432015-08-04
01-0442015-09-29
01-0452015-10-05
01-0462015-06-09
01-0472015-10-15
01-0482015-10-14
01-0492015-10-21
01-0502015-10-27
01-0512015-10-28
01-0522015-10-30
01-0532015-11-03
01-0552015-11-03
01-0562015-11-06
01-0572015-11-20
01-0582015-11-20
01-0592015-11-20
01-0612015-11-30
01-0622015-12-18
01-0632016-01-19
01-0662016-02-04
01-0702016-03-16
02-0142015-02-24
02-0152015-03-12
02-0162015-03-17
02-0172015-03-19
02-0182015-03-24
02-0192015-03-31
02-0212015-03-12
02-0222015-03-05
02-0232015-05-19
02-0242015-05-11
02-0252015-06-11
02-0262015-06-16
02-0272015-08-13
02-0282015-08-04
02-0292015-11-17
02-0302015-12-01
02-0312015-07-28
02-0322016-02-16
01-0642016-02-02
01-0652016-02-03
01-0682016-02-16
01-0692016-02-24
01-0712016-03-30
01-0722016-04-05
01-0732016-04-13
01-0742016-04-28
01-0752016-05-13
01-0762016-06-10
01-0772016-06-14
01-0782016-06-16
01-0792016-06-23
01-0802016-07-05
01-0812016-07-19
01-0822016-07-26
01-0832016-07-27
01-0842016-08-08
01-0852016-08-02
01-0862016-08-10
01-0872016-09-06
01-0892016-09-20
01-0902016-09-22
01-0912016-10-21
01-0922016-11-21
01-0932016-11-22
01-0942016-11-23
01-0972017-01-26
01-0982017-01-29
01-0992017-01-29
01-1022017-02-10
02-0342016-05-03
02-0352016-04-28
02-0362016-05-10
02-0372016-05-12
02-0382016-07-07
02-0392016-07-21
02-0402016-05-19
02-0412016-09-08
02-0422016-06-09
02-0432016-08-30
02-0442016-08-09
02-0452016-09-29
02-0462016-10-12
02-0472016-12-01
02-0482016-12-08
02-0492017-01-05
02-0502017-01-24
02-0512016-11-30
02-0522017-01-26
01-1002017-02-02
01-1012017-02-10
01-1032017-02-14
01-1042017-02-24
01-1052017-03-01
01-1062017-03-09
01-1072017-03-09
01-1082017-03-22
01-1092017-04-03
01-1102017-04-05
01-1122017-04-13
01-1142017-04-24
01-1152017-05-10
01-1162017-05-15
01-1172017-05-15
01-1182017-05-30
01-1232017-06-21
01-1272017-07-10
02-0532017-04-20

Accepted Solutions
Solution
‎09-21-2017 11:25 AM
Trusted Advisor
Posts: 1,148

Re: How to calculate the relative month for patient recruitment period (a new continuous variable)

[ Edited ]

You can use the INTCK INTNX function to calculate the number of month boundaries (i.e. jan to feb, feb to mar, ... dec to jan) crossed since jan 31, 2014 up to the recruitement_date.   That would make all recruitments in Feb 2014 get a value of 1, in Mar 2014 get a value of 2, etc, as in:

 

data want;
  set have;

  month_number=intck('month','01jan2014'd,recruitment_date);

run;

 

This assumes you have admission_date stored as a sas date value.

 

Thanks @Astounding.   I've replaced the erroneous intnx with the intended INTCK.

View solution in original post


All Replies
Solution
‎09-21-2017 11:25 AM
Trusted Advisor
Posts: 1,148

Re: How to calculate the relative month for patient recruitment period (a new continuous variable)

[ Edited ]

You can use the INTCK INTNX function to calculate the number of month boundaries (i.e. jan to feb, feb to mar, ... dec to jan) crossed since jan 31, 2014 up to the recruitement_date.   That would make all recruitments in Feb 2014 get a value of 1, in Mar 2014 get a value of 2, etc, as in:

 

data want;
  set have;

  month_number=intck('month','01jan2014'd,recruitment_date);

run;

 

This assumes you have admission_date stored as a sas date value.

 

Thanks @Astounding.   I've replaced the erroneous intnx with the intended INTCK.

Super User
Posts: 5,987

Re: How to calculate the relative month for patient recruitment period (a new continuous variable)

Good idea, but I think you meant INTCK instead of INTNX.

Frequent Contributor
Posts: 110

Re: How to calculate the relative month for patient recruitment period (a new continuous variable)

[ Edited ]

Try this...

 

First let me build a sample data.

data have;
  do y=2016 to 2018;
   do m= 1 to 12;
   recruit_dt=mdy(i,1,y);
format recruit_dt date9.;
  end;
  end;
run;

Second - remove records below 01Feb2017 like this

data have (where=(recruit_dt  >= '01Feb2017'd));
  do y=2016 to 2018;
   do m= 1 to 12;
   recruit_dt=mdy(i,1,y);
format recruit_dt date9.;
  end;
  end;
run;

Third compute the month.

data have (where=(recruit_dt  >= '01Feb2017'd));
  do y=2016 to 2018;
   do m= 1 to 12;
   recruit_dt=mdy(i,1,y);
  month=intck('month','01Feb2017'd,recruit_dt);
 format recruit_dt date9.;
  end;
  end;
run;

Notice that month=0 for 01Feb2017?  We correct that using this..

 

data have (where=(recruit_dt  >= '01Feb2017'd));
  do y=2016 to 2018;
   do m= 1 to 12;
   recruit_dt=mdy(i,1,y);
  month=intck('month','01Feb2017'd,recruit_dt)+1;
 format recruit_dt date9.;
  end;
  end;
run;

The last dataset should help you do this one.

 

Hope this helps.

Super User
Posts: 10,313

Re: How to calculate the relative month for patient recruitment period (a new continuous variable)

You didn't post the output yet.


data have;
 do i='01feb2015'd to '30sep2017'd;
   month=month(i);
   output;
 end; 
 format i date9.;
run;
data want;
 set have;
 by month notsorted;
 new_month+first.month;
run;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 162 views
  • 0 likes
  • 5 in conversation