DATA Step, Macro, Functions and more

counting months

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 110
Accepted Solution

counting months

I have a range of dates.  I would like to count how many months are within each set of dates along with creating a new variable for each month. For example;

data have;

input start_date end_date;

01/01/2010 12/31/2010

05/01/2010 12/31/2010

05/01/2010 05/14/2010

06/13/2010 08/28/2010;

run;

I would like to create a new variable for each month that contains the counts for each number of months between the start and end dates. For example;

01/01/2010 12/31/2010 --> month1=1 month2=1, month3=1 . . . .month12=1;

05/01/2010 12/31/2010 --> month5=1 month6=1, month7=1 . . . .month12=1;

05/01/2010 05/14/2010 --> month5=1;

06/13/2010 08/28/2010 --> month6=1 month7=1 month8=1;

Based on this sample data, in the end I should have the below counts for each month.

Month1=1

Month2=1

Month3=1

Month4=1

Month5=3

Month6=3

Month7=3

Month8=3

Month9=2

Month10=2

Month11=2

Month12=2

Thank you!

Sophia


Accepted Solutions
Solution
‎04-17-2013 01:39 PM
Super User
Posts: 11,343

Re: counting months

Posted in reply to sophia_SAS

Are all of your data pairs always within the same calendar year? Are the dates SAS date values or strings?

For your example this seems to work.

 

data have;

informat start_date end_date mmddyy10.;

format start_date end_date mmddyy10.;

input start_date end_date;

datalines;

01/01/2010 12/31/2010

05/01/2010 12/31/2010

05/01/2010 05/14/2010

06/13/2010 08/28/2010

;

run;

data want;

set have;

array months month1-month12;

do i= month(start_date) to month(end_date);

months=1;

end;

drop i;

run;

View solution in original post


All Replies
Solution
‎04-17-2013 01:39 PM
Super User
Posts: 11,343

Re: counting months

Posted in reply to sophia_SAS

Are all of your data pairs always within the same calendar year? Are the dates SAS date values or strings?

For your example this seems to work.

 

data have;

informat start_date end_date mmddyy10.;

format start_date end_date mmddyy10.;

input start_date end_date;

datalines;

01/01/2010 12/31/2010

05/01/2010 12/31/2010

05/01/2010 05/14/2010

06/13/2010 08/28/2010

;

run;

data want;

set have;

array months month1-month12;

do i= month(start_date) to month(end_date);

months=1;

end;

drop i;

run;

Frequent Contributor
Posts: 110

Re: counting months

Thanks!  That worked perfectly.

🔒 This topic is solved and locked.

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

Discussion stats
  • 2 replies
  • 344 views
  • 0 likes
  • 2 in conversation