Macro DateDiff

From sasCommunity
Jump to: navigation, search

Macro DateDiff

 /*    name: DateDiff
description: date difference
purpose    :
This SAS macro will return the differencde between two
dates in day, months, and years.
Beg_Date is the beginning date and must be specified.
End_Date is the ending date and will default to the system date
if not specified.
The difference between the two dates will be returned
in three variables: Days, Months, and Years.
 
notes      :
This macro can calcualte date differences using one of two algorithms,
intuitive or mathematical.
Specifiay Type of INTU or MATH.
If Type is not specified,
the macro will default to the intuitive algorithm.
 
Type = Math
The result will be mathematically accurate
but not always intuitively correct.
For example, the difference between 1/1/80 and 3/1/80
will result in 1 month and 30 days, not 2 months
which may seem more reasonable.
This is due to using an average year of 365.2425 days,
which is based on 146,097 days in a 400 year cycle.
 
Type = INTU
The result will always be intuitively correct
but may not always be mathematically accurate.
For example, the difference between 1/1/80 and 3/1/80
will result in exactly 2 months.
 
protocol:
%DateDiff(<type     = INTU | MATH >
           Beg_date = SAS_Beginning_Date
          <End_Date = SAS_Ending_Date >
                      default: system date
          <Days     = Days_Place_Holder >
                      default: Days
          <Months   = Months_Place_Holder >
                      default: Months
          <Years    = Years_Place_Holder >
                      default: Years
         );
 
example(s):
%DateDiff(Beg_date = mdy(5,01,64));
 
%DateDiff(type     = MATH
         ,Beg_date = mdy(5,01,64)
         ,End_Date = mdy(1,1,90)
         ,Days     = Num_Days
         ,Months   = Num_Months
         ,Years    = Num_Years
         );
 
author      : Jerry Kagan
date created: January 1991
 
DateDiff: More than a SAS(R) Macro
sugi17.123
**************************************** */
%macro DateDiff
(type     = INTU
,Beg_date =
,End_Date = today()
,Days     = Days
,Months   = Months
,Years    = Years
);
%Let type = %upcase(&Type.);
 
%local dec_year ; %let dec_year  = 365.2425;
%local dec_month; %let dec_month = 30.436875;
 
%* error: no begin date;
%if not %length(%quote(&Beg_Date.)) %then
    %put Error: &sysmacroname. missing Beg_Date;
 
%* drop temporary variables;
drop _Diff
     _BDay   _BMonth _BYear
     _EDay   _EMonth _EYear
             _TempMo _TempYr
     _Sign;
 
%* calculation:
   if type = math then subtract BegDate from EndDate
   using the math algorithm,
   and an average year of &Dec_year. days;
 
%if %upcase(%substr(&Type.,1,4)) eq MATH %then %do;
    %put note: &sysmacroname. using Math algorithm;
    %* _Diff is a SAS date value representing the difference
       between the two dates in exactly the correct number of days;
    _Diff = (&End_Date. - &Beg_Date);
    %* calculate &Years. using an average year;
    &Years = int(_Diff/&Dec_Year.);
    %* calculate &Months. using an average year/12;
    &Months = int(_Diff/&Dec_Month. - &Years * 12);
    %* subtracting &Months and ^Years results in &Days.;
    &Days = round(_Diff
                 - &Years.  * &Dec_Year.
                 - &Months. * &Dec_Month.);
    %end;
%else %do;
    %put note: &sysmacroname. using Intuitive algorithm;
    %* if Beg_Date is greater than End_Date, swap them and set _Sign;
    if &Beg_Date. gt &End_Date. then do;
       _Sign = &Beg_Date.;
       &Beg_Date. = &End_Date.;
       &End_Date. = _Sign;
       _Sign      = -1;
       end;
    else _Sign    =  1;
    %end;
 
%* break up each date into days, months and years.;
_BDay   =   day(&Beg_Date.);
_BMonth = month(&Beg_Date.);
_BYear  =  year(&Beg_Date.);
 
_EDay   =   day(&End_Date.);
_EMonth = month(&End_Date.);
_EYear  =  year(&End_Date.);
 
%* starting with the days position, subtract _Bday from _EDay.
   if _EDay is smaller than _Bday, borrow from the months position.
   Note: the number of days to carry
        shold be the number of days in the month of _BMonth,
        not numbe of days inteh month of _EMonth.
        This is calculated by converting day 1 of the month
        after _BMonth into a SAS date using the mdy functin.
        then subtracting 1 from this SAS data results in a SAS date
        number for the last day of the month of _BMonth.
        Using the day function then returns the number of days
        in the month _BMonth,
        which is the correct number of days to carry.;
if _EDay lt _BDay then do;
   %* borrow a month from _EMonth and carry the days;
   if _EMonth eq 1 then do;
      _EYear + (-1);
      _EMonth = 12;
      end;
   else _EMonth + (-1);
   %* calculate the number of carry days;
   if _BMonth = 12 then do;
      _TempYr = _BYear + 1;
      _TempMo = 1;
      end;
   else do;
      _TempYr = _BYear;
      _TempMo = _BMonth + 1;
      end;
   %* add carry days to _EDay;
   %* RJF2 note: _EDay is retained;
   _EDay + day(mdy(_TempMo,1,_TempYr) -1);
   end;
%* calculate days position;
&Days = (_EDay + (- _Bday)) * _Sign;
 
%* moving to the months position,
   substract _BMonth from _EMonth.
   if _EMonth is smaller than _BMonth,
      borrow from the years position.;
if _EMonth lt _BMonth then do;
   _EYear + (-1);
   _EMonth + 12;
   end;
 
%* calculate month position;
&Months = (_EMonth - _BMonth) * _Sign;
 
%* finally subtract _BYear from _EYear to calculate the year position.;
&Years = (_EYear - _BYear) * _Sign;
 
%* if _Sign is negative, swap &Beg_Date and End_Date;
if _Sign lt 0 then do;
   _Sign     = &Beg_Date.;
   &Beg_Date = &End_Date.;
   &End_Date = _Sign;
   end;
 
%mend DateDiff;

Testing program

* name: Datediff-Test;
%Include 'Datediff.sas';
options mprint;
*options macrogen;
 
DATA   Work.Dates(drop = Start Interval);
attrib Beg_Date length = 8 format = mmddyy8.
       End_Date length = 8 format = mmddyy8.;
 
Start    = mdy(05,27,1979);
End_Date = mdy(06,30,1979);
Interval = 1;
 
do Beg_Date = Start to End_Date by Interval;
   * calculate the math difference;
   %DateDiff(type     = math
            ,beg_date = Beg_Date
            ,end_date = End_Date
            ,days     = M_Days
            ,months   = M_Months
            ,years    = M_Years
            );
   %DateDiff(type     = intuitive
            ,beg_date = Beg_Date
            ,end_date = End_Date
            ,days     = I_Days
            ,months   = I_Months
            ,years    = I_Years
            );
   * calculate the exact difference in days;
   Diff_Day = End_Date - Beg_Date;
   * calculate the difference in average years.;
   Diff_Yr = Diff_Day / 365.2425;
   output;
   end;
stop;
 
PROC Print data = &SysLast.;
           title3 &SysLast.;

Macro diff_date

http://staff.washington.edu/billyk/TechTips_SC4Q98.pdf

> -----Original Message-----
> From: Daniel Nordlund
> Sent: Tuesday, May 19, 2009 2:12 AM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: RE: SUGI 17 and datediff function

> The guts of the calculations are based 
> on the well known calculation of age 
> formulated by Billy Kreuter.

Program:

%macro diff_date
(begin = 
,end   = today()
,y     = years
,m     = months
,d     = days
);
*--number of full months between dates;
&m = intck('month', &begin, &end)
     -(day(&end) < day(&begin));
*--number of full years between dates;
&y = floor(&m / 12);
*--remainder of full months after removing full years;
&m = mod(&m , 12);
*--number of days remaining after removing years and months;
&d = ( day(&end) < day(&begin)) 
      * day(intnx('month',&begin,1)-1) 
      + day(&end) 
      - day(&begin);
%mend;

Simple Data Step Usage

data _null_;
begin_date = '31jan2008'd;
end_date = '01mar2009'd;
%diff_date(begin=begin_date, end=end_date)
put years= months= days= ;
run;

References

harvested by:

--macro maven == the radical programmer 22:43, 18 May 2009 (UTC)