topic Re: Month between two dates in SAS Programming

Month between two dates

aguilar_john — Fri, 18 Dec 2020 19:37:37 GMT

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?

Re: Month between two dates

ballardw — Fri, 18 Dec 2020 19:48:57 GMT

What do expect the answer to be?

Re: Month between two dates

aguilar_john — Fri, 18 Dec 2020 19:52:03 GMT

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

Re: Month between two dates

novinosrin — Fri, 18 Dec 2020 20:01:03 GMT

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;

Re: Month between two dates

aguilar_john — Fri, 18 Dec 2020 20:04:05 GMT

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?

Re: Month between two dates

aguilar_john — Fri, 18 Dec 2020 20:22:48 GMT

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?

Re: Month between two dates

ballardw — Fri, 18 Dec 2020 20:35:37 GMT

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;

Re: Month between two dates

aguilar_john — Fri, 18 Dec 2020 20:51:33 GMT

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.

Re: Month between two dates

ballardw — Fri, 18 Dec 2020 21:12:57 GMT

@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

Re: Month between two dates

mkeintz — Sat, 19 Dec 2020 02:20:28 GMT

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.

feb/mar
mar/apr
apr/may
may/jun
jun/jul
jul/aug
aug/sep
sep/oct
oct/nov
nov/dec
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:

10mar2021
10apr2021
....

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.

Re: Month between two dates

Ksharp — Sun, 20 Dec 2020 12:59:15 GMT

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;

Re: Month between two dates

aguilar_john — Tue, 22 Dec 2020 15:04:02 GMT

Thank you for the elaborate answer.

Re: Month between two dates

aguilar_john — Tue, 22 Dec 2020 15:06:00 GMT

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.

Re: Month between two dates

Tom — Tue, 22 Dec 2020 16:26:57 GMT

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