BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Denali
Quartz | Level 8

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

Accepted Solutions
mkeintz
PROC Star

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

--------------------------

View solution in original post

4 REPLIES 4
mkeintz
PROC Star

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

--------------------------
Astounding
PROC Star

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

ShiroAmada
Lapis Lazuli | Level 10

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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