SAS DATE issue - calculating "NEAREST MONTH"

Question

I need to calculate a figure which equates to the 'nearest' number of months between two dates. However the standard SAS function (INTCK) is not geared to consider the DAY of its date parameters (eg code below resolves to 0 when I need it to round to 1).

What is the 'neatest' way of resolving this issue?

data _null_;
 x="01APR08"d;
 y="28APR08"d;
 z=intck('MONTH',x,y);
 put z= ;
run;

EDIT: response to Martins comment.

I would round to 0 months - I don't think the border is relevant. The function I am trying to replicate (NEAREST_MONTHS) comes from DCS (Sungard prophet application). Am now awaiting the chance to perform some testing within the application itself to understand more about how it treats dates (will post results back here ) ).

The help file contains the following: Category Date

Description

Returns the difference between two dates to the nearest number of months. If the second date is later than the first date then 0 is returned.

Syntax

NEAREST_MONTHS(Later_Date, Earlier_Date)

Return Type Integer

Examples

NEAREST_MONTHS(date1, date2) Returns 8 if date1 is 20/3/1997 and date2 is 23/7/1996

NEAREST_MONTHS(date1, date2) Returns 26 if date1 is 20/3/1997 and date2 is 1/2/1995

Answer 1

You could use INTNX to see whether to round up or down, e.g.

data _null_;
  format x y date9. z 8.;
  x="01APR08"d;
  y="28APR08"d;
  z=intck('MONTH',x,y);

  * wl is x + z months;
  wl=intnx('MONTH',x,z);

  * wu is x + (z+1) months;
  wu=intnx('MONTH',x,z+1);

  * If y is closer to wu, then adjust z by 1;
  if (abs(y-wu) lt abs(y-wl)) then z = z+1;  

  put x y z=;
run;

Answer 2

If you define a month to be 30 days, you would round 15 days or less down to 0 months, and 16 days or more up to 1 month. This can be achieved by the following:

data _null_;
  format x y date9. z 8.;
  x="14FEB09"d;
  y="02MAR09"d;

  z=round(intck('DAY',x,y)/31);
  put x y z=;
run;

You could also take the approach to count the full months ("first 1st to last 1st") in the interval, and then add up any remaining days to see if they sum up to 0, 1 or 2 months. Like this:

data _null_;
  format x y date9. z 8.;
  x="01FEB09"d;
  y="31MAR09"d;

  if day(x)=1 then do;
     z=intck('MONTH',x,intnx('MONTH',y,0,'BEGINNING'))
         + round((intck('DAY',intnx('MONTH',y,0,'BEGINNING'),y))/31);
  end;
  else do;
     z=intck('MONTH',intnx('MONTH',x,1,'BEGINNING'),intnx('MONTH',y,0,'BEGINNING'))
         + round((intck('DAY',x,intnx('MONTH',x,1,'BEGINNING'))+intck('DAY',intnx('MONTH',y,0,'BEGINNING'),y))/31);
  end;
  put x y z=;
run;

The first method is easier to understand and maintain, but the second is more accurate for large intervals (01FEB06 to 01FEB09 is 36 months, but method 1 will tell you it's only 35).

Answer 3

I wrote this as a function that I think calculates in the same way as the DCS application. It uses some features that are new to SAS in version 9.2 including continuous alignments in dates. It also works forwards or backwards in time (i.e gives a negative integer if earlier_date is after later_date). I used more than 15 days beyond the interval as the cutoff to round to the next month but you can tweak this if you prefer.

proc fcmp outlib=work.myfuncs.dates;
   function nearest_months(later_date,earlier_date);
        /* Return missing if inputs are missing */
        if (earlier_date eq . ) OR (later_date eq . ) then
            nearest_month=.;
        else do; 
            /* Use 'cont' argument for continuous dates */
            months=intck('MONTH',earlier_date,later_date,'cont');
            if months < 0 then months=months+1;
            days= later_date - intnx('month', earlier_date,months,'same');

            /* Handle negatives (earlier dates) */
            if months < 0 then do;
                if days < -15 then months=months-1;
                nearest_month=months;
                end;
            else do;
                if days > 15 then months + 1;
                nearest_month=months;
                end;
        end;
        return(nearest_month);
   endsub;
run;
options cmplib=work.myfuncs;


data _null_;
x=nearest_months('20Mar1997'd, '23JUL1996'd);
put x=;
x=nearest_months('20Mar1997'd, '01FEB1995'd);
put x=;
run;

This gives the same as your reference:

x=8
x=26