topic Rounding issue in datastep in SAS Programming

Rounding issue in datastep

Q1983 — Wed, 04 Apr 2018 19:57:13 GMT

data have;

format stop_date date9.;

input ln_no $ stop_date date9. ;

datalines;

1123 16jun2014

1124 28dec2017

;

run;

data have2;

set have;

months = intck('Month',stop_date,"4apr2018"d);

run;

Output

ID Date months

1123 6jun2014 46

1124 28dec2017 4

I received code that asks for a rounding of the above numbers

round(months_between("&today."d, stop_date),2)

I have not heard of a months_between function so I used the intck function. The code received yields these results

Output

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

Re: Rounding issue in datastep

PaigeMiller — Wed, 04 Apr 2018 20:21:55 GMT

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.

Re: Rounding issue in datastep

Kurt_Bremser — Wed, 04 Apr 2018 21:04:17 GMT

The months_between() function is most probably custom made and returns a bogus value. Bogus for the reasons @PaigeMiller gave.

Re: Rounding issue in datastep

PaigeMiller — Wed, 04 Apr 2018 21:36:05 GMT

@Kurt_Bremser, it certainly is not a SAS function, but it is an Oracle/PLSQL function (and probably exists in other languages as well).

https://www.techonthenet.com/oracle/functions/months_between.php

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.

Re: Rounding issue in datastep

PGStats — Wed, 04 Apr 2018 22:13:02 GMT

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.

Re: Rounding issue in datastep

ballardw — Wed, 04 Apr 2018 22:18:58 GMT

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;