/* 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;
* 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.;
> -----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.
%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;
--macro maven == the radical programmer 22:43, 18 May 2009 (UTC)