Quartz | Level 8

## 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-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
1 ACCEPTED SOLUTION

Accepted Solutions
PROC Star

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

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
4 REPLIES 4
PROC Star

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

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
PROC Star

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

Lapis Lazuli | Level 10

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

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

Hope this helps.

Super User

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

```
Discussion stats
• 4 replies
• 676 views
• 0 likes
• 5 in conversation