04-04-2018 03:57 PM
format stop_date date9.;
input ln_no $ stop_date date9. ;
months = intck('Month',stop_date,"4apr2018"d);
ID Date months
1123 6jun2014 46
1124 28dec2017 4
I received code that asks for a rounding of the above numbers
I have not heard of a months_between function so I used the intck function. The code received yields these results
ID Date months
1123 6jun2014 45.61
1124 28dec2017 3.23
It appears the above code rounds down and sends two decimal places. I am attempting to determine how to replicate these exact numbers. The analyst that prepared this code is no longer available
04-04-2018 04:21 PM - edited 04-04-2018 04:21 PM
First, you haven't shared with us variable STOP_DATE
Second, the concept of fractional months is something I would avoid like the plague. You'd have to handle months of 28, 29, 30 and 31 days properly, and even if you did that, the interpretation of fractional months is meaningless (is 18.5 months equal to 18 months and 14 days, or 18 months and 15 days, or 18 months and 15.5 days?) What a mess.
04-04-2018 05:04 PM
The months_between() function is most probably custom made and returns a bogus value. Bogus for the reasons @PaigeMiller gave.
04-04-2018 05:36 PM
@KurtBremser, it certainly is not a SAS function, but it is an Oracle/PLSQL function (and probably exists in other languages as well).
it says right there that if a fractional month is calculated, a 31-day month is assumed, but it's still not clear what good that is if your time period has months of different lengths, interpretation again becomes a problem.
04-04-2018 06:13 PM
Using a 2-decimals correct value of the average month duration you get:
data have; format stop_date yymmdd10.; input ln_no $ stop_date date9. ; datalines; 1123 16jun2014 1124 28dec2017 ; data want; set have; intMonths = intck("month", stop_date, today(), "continuous"); months = round( intMonths + intck("day", intnx("month", stop_date, intMonths, "same"), today()) * 12 / 365.25 , 0.01); drop intMonths; run; proc print; run;
Obs stop_date ln_no months 1 2014-06-16 1123 45.62 2 2017-12-28 1124 3.23
As calculated on the 4th of April 2018.
04-04-2018 06:18 PM
And rounding the number of months returned to the nearest multiple of 2 as well? Very interesting.
Perhaps you are not aware that the intck and intnx functions can use multiples. Also Intck has two different methods of comparison, Continuous or Discrete with Discrete the default. The following generates the number of "two month" intervals using default and both methods explicitly.
data have; format stop_date date9.; input ln_no $ stop_date date9. ; months = intck('Month2',stop_date,"4apr2018"d); months2 = intck('Month2',stop_date,"4apr2018"d,'C'); months3 = intck('Month2',stop_date,"4apr2018"d,'D'); datalines; 1123 16jun2014 1124 28dec2017 ; run;