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

[Denali]

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-001	2014-02-07
01-002	2014-02-21
01-003	2014-03-28
01-004	2014-03-25
01-005	2014-03-25
01-006	2014-03-28
01-007	2014-04-22
01-008	2014-05-27
01-009	2014-05-28
01-010	2014-06-20
01-011	2014-07-01
01-012	2014-07-24
01-013	2014-08-12
01-014	2014-09-26
01-015	2014-10-02
01-016	2014-11-06
01-017	2014-11-18
01-018	2014-10-31
01-019	2014-12-05
01-020	2014-12-22
01-028	2015-03-16
01-054	2014-10-02
01-060	2014-07-03
02-001	2014-03-20
02-002	2014-04-01
02-004	2014-05-22
02-005	2014-07-17
02-006	2014-04-03
02-007	2014-07-22
02-008	2014-09-18
02-009	2014-09-11
02-010	2014-09-25
02-011	2014-10-28
02-012	2014-11-06
02-013	2015-01-15
01-022	2015-02-09
01-023	2015-01-26
01-026	2015-03-16
01-027	2015-03-20
01-029	2015-03-25
01-030	2015-04-27
01-031	2015-05-01
01-032	2015-05-06
01-033	2015-05-11
01-035	2015-06-12
01-036	2015-06-18
01-037	2015-06-17
01-038	2015-07-20
01-039	2015-07-31
01-040	2015-08-12
01-041	2015-09-10
01-042	2015-08-18
01-043	2015-08-04
01-044	2015-09-29
01-045	2015-10-05
01-046	2015-06-09
01-047	2015-10-15
01-048	2015-10-14
01-049	2015-10-21
01-050	2015-10-27
01-051	2015-10-28
01-052	2015-10-30
01-053	2015-11-03
01-055	2015-11-03
01-056	2015-11-06
01-057	2015-11-20
01-058	2015-11-20
01-059	2015-11-20
01-061	2015-11-30
01-062	2015-12-18
01-063	2016-01-19
01-066	2016-02-04
01-070	2016-03-16
02-014	2015-02-24
02-015	2015-03-12
02-016	2015-03-17
02-017	2015-03-19
02-018	2015-03-24
02-019	2015-03-31
02-021	2015-03-12
02-022	2015-03-05
02-023	2015-05-19
02-024	2015-05-11
02-025	2015-06-11
02-026	2015-06-16
02-027	2015-08-13
02-028	2015-08-04
02-029	2015-11-17
02-030	2015-12-01
02-031	2015-07-28
02-032	2016-02-16
01-064	2016-02-02
01-065	2016-02-03
01-068	2016-02-16
01-069	2016-02-24
01-071	2016-03-30
01-072	2016-04-05
01-073	2016-04-13
01-074	2016-04-28
01-075	2016-05-13
01-076	2016-06-10
01-077	2016-06-14
01-078	2016-06-16
01-079	2016-06-23
01-080	2016-07-05
01-081	2016-07-19
01-082	2016-07-26
01-083	2016-07-27
01-084	2016-08-08
01-085	2016-08-02
01-086	2016-08-10
01-087	2016-09-06
01-089	2016-09-20
01-090	2016-09-22
01-091	2016-10-21
01-092	2016-11-21
01-093	2016-11-22
01-094	2016-11-23
01-097	2017-01-26
01-098	2017-01-29
01-099	2017-01-29
01-102	2017-02-10
02-034	2016-05-03
02-035	2016-04-28
02-036	2016-05-10
02-037	2016-05-12
02-038	2016-07-07
02-039	2016-07-21
02-040	2016-05-19
02-041	2016-09-08
02-042	2016-06-09
02-043	2016-08-30
02-044	2016-08-09
02-045	2016-09-29
02-046	2016-10-12
02-047	2016-12-01
02-048	2016-12-08
02-049	2017-01-05
02-050	2017-01-24
02-051	2016-11-30
02-052	2017-01-26
01-100	2017-02-02
01-101	2017-02-10
01-103	2017-02-14
01-104	2017-02-24
01-105	2017-03-01
01-106	2017-03-09
01-107	2017-03-09
01-108	2017-03-22
01-109	2017-04-03
01-110	2017-04-05
01-112	2017-04-13
01-114	2017-04-24
01-115	2017-05-10
01-116	2017-05-15
01-117	2017-05-15
01-118	2017-05-30
01-123	2017-06-21
01-127	2017-07-10
02-053	2017-04-20

[mkeintz · Accepted solution]

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.

[Astounding]

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

[ShiroAmada]

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.

[Ksharp]

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;