Month between two dates

[aguilar_john]

Hello everyone,

it seems that the SAS intck function has a problem when calculating the difference between two dates within a month. 

I want to calculate the month between 01FEB2021 and 31JAN2022, but even with the continous option the result is 11 month.

data test;
	date=intck("month",'01FEB2021'd,'31JAN2022'd,'c');
run;

 Any ideas how to solve this?

[ballardw]

What do expect the answer to be?

[aguilar_john]

12
feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec,jan

[novinosrin]

Hi @aguilar_john  Please be aware your interval doesn't exceed 364 days or in other words doesn't complete a full 12 month period

	date=intck("month",'01FEB2021'd,'01feb2022'd,'c');

12th month elapses on 01feb2022

Alternatively if you mean inclusive of start date then-

date=intck("month",'01FEB2021'd,'31JAN2022'd,'c')+1;

[aguilar_john]

Hi @novinosrin thank you for your answer. But logically between 01FEB2021 and 31JAN2022 you have 12 month. Is there another way to calculate the correct difference?

[aguilar_john]

The +1 at the end also came to mind, but since I have to do this calculation for great number of different dates, I am not sure if the +1 I would solve this case but show another case wrong. Any other ideas or more general solution?

[ballardw]

Lets go to a very simple example:

data test;
d1=intck("month",'31JAN2020'd,'01Feb2020'd);
d2=intck("month",'31JAN2020'd,'01Feb2020'd,'c');
d3=intck("month",'31JAN2020'd,'28Feb2020'd,'c');
d4=intck("month",'31JAN2020'd,'29Feb2020'd,'c');
d5=intck("month",'31JAN2020'd,'01Mar2020'd,'c');
run;

D1 has a value of 1. Is that correct for your interpretation of month?

D2 has a value of 0. How does that relate to your interpretation.

D3 is 0 because it is not the equivalent of the end of the month yet.

D4 is 1 because an entire "month" has now passed.

D5 is still one, the result continues to be 1 until the end of March.

So in your calculation the month does not advance until the next actual first of the month is encountered, ie 01 Feb.

You can see some of this in the related INTNX function advance a 01Feb date 11 months and see the result:

data example;
  date = '01Feb2021'd;
  d2 = intnx('month',date,11,'b');
  d3 = intnx('month',date,11,'e');
  d4 = intnx('month',date,11,'s');
  format d: date9.;
run;

[aguilar_john]

Hi @ballardw thank you for the answer. This however does not solve my problem. In your first example d3 d3=intck("month",'31JAN2020'd,'28Feb2020'd,'c'); I would also expect the value to be 1.
But it seems that there is no general way to show this with SAS.

d2=intck("month",'31JAN2020'd,'01Feb2020'd,'c'); 

would not exist, cause for my purpose always the whole month for the enddate would be relevant.  

[ballardw]

---

@aguilar_john wrote:

Hi @ballardw thank you for the answer. This however does not solve my problem. In your first example d3 d3=intck("month",'31JAN2020'd,'28Feb2020'd,'c'); I would also expect the value to be 1.
But it seems that there is no general way to show this with SAS.

d2=intck("month",'31JAN2020'd,'01Feb2020'd,'c'); 

would not exist, cause for my purpose always the whole month for the enddate would be relevant.  

---

 Are all of your actual dates first/ last of the month? Then maybe the 'C' option is not for you.

I'm not really sure what your rules are. It may be that you could use a different interval like "weeks" and some arithmetic if the +1 to the month interval doesn't work. Or creative use of INTNX plus INTCK

[mkeintz]

The INTCK function just counts the number of month boundaries crossed between the 2 dates (except if you use the "continuous" method as the 4th argument).

So  intck('month','01feb20201d,'31jan2022'd) must be 11, because that is the number of boundaries crossed.

1. feb/mar
2. mar/apr
3. apr/may
4. may/jun
5. jun/jul
6. jul/aug
7. aug/sep
8. sep/oct
9. oct/nov
10. nov/dec
11. dec/jan

Editted note: Sorry, didn't paste in my second example, which was more relevant.

In the case of 'continuous', the first date establishes the month "boundary".  In your case, it's the number of 01mmm dates covered between the dates, which is the same as "discrete". 

Try continuous starting with, say 10feb2021.  Then it's the number of 10mmm's encountered:

1. 10mar2021
2. 10apr2021
3. ....

    11. 10jan2022

Of course, it's not quite as simple if the starting date is 29,30 or 31,because shorter months must be accommodated.  Effectively there must be a boundary identified in each month - either it's the day component of the first date, or if no such day exists for a given month, it's the normal discrete month boundary.

[aguilar_john]

Thank you for the elaborate answer.

[Ksharp]

It was supposed  to be ,you didn't reach 01feb2020. If you want 12 ,just add 1 at end of date.

data test;
	date=intck("month",'01FEB2021'd,'31JAN2022'd +1,'c');
run;

[aguilar_john]

Thank you for the answer, this approach was also mentioned by @novinosrin, but since I have to do this calculation for many different dates I am not sure if the +1 will always show the correct value for me.

[Tom]

---

@aguilar_john wrote:
Thank you for the answer, this approach was also mentioned by @novinosrin, but since I have to do this calculation for many different dates I am not sure if the +1 will always show the correct value for me.

---

Then run an experiment and check.

data test;
   do start='01JAN2020'd to '31JAN2020'd ;
      do end='01MAR2020'd to '31MAR2020'd ;
         d1=intnx('month',start,end,'c');
         d2=intnx('month',start,end+1,'c');
         if not (d1=d2) then output;
    end;
  end;
  format start end date9.;
run;