DATA Step, Macro, Functions and more

Rounding issue in datastep

Reply
Regular Contributor
Posts: 180

Rounding issue in datastep

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

 

 

Respected Advisor
Posts: 3,278

Re: Rounding issue in datastep

[ Edited ]

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.

--
Paige Miller
Super User
Posts: 10,574

Re: Rounding issue in datastep

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Respected Advisor
Posts: 3,278

Re: Rounding issue in datastep

Posted in reply to KurtBremser

@KurtBremser, 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.

--
Paige Miller
Esteemed Advisor
Posts: 5,625

Re: Rounding issue in datastep

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.

PG
Super User
Posts: 13,941

Re: Rounding issue in datastep

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;
Ask a Question
Discussion stats
  • 5 replies
  • 126 views
  • 2 likes
  • 5 in conversation