topic Re: How to round Interval date in SAS Programming

How to round Interval date

adil256 — Thu, 13 Jul 2017 14:44:42 GMT

Hi everone,

I got an issue when it comes to computed date interval in month.

I would like to round up the month when 1 day or more have elapsed during the month (ex: 3months and 1 day should be considered as 4months; 2 months and 5 days = 3 months; 11 months and 0 days should be 11 months).

I'm using this function but it only round up when the half of the month elapsed.

Data one;
 input V U;
 informat V U date9.; 
format V U date9.; 
infile datalines missover; 
datalines; 
15feb2012 1jan2012
15feb2012 14jan2012
15feb2012 15jan2012
15apr2012 15jan2012
; 
run; 
data one;
 set one;
 duration = max(int((V-U)/(365.25/12)+0.5),1);
 run; 

Data want;
 input V U duration;
 informat V U date9. duration;
 format V U date9.; 
infile datalines missover; 
datalines; 
15feb2012 1jan2012 2
15feb2012 14jan2012 2
15feb2012 15jan2012 1
15apr2012 15jan2012 3
; 
run;

Any help would be grateful. Thank you.

Re: How to round Interval date

RW9 — Thu, 13 Jul 2017 15:03:12 GMT

Sorry, that doesn't make sense, and the code you provided doesn't work. What is the duration variable for? If you just want to round up month then use intck('month',date,1) on a date variable to move it one month forward, dates cannot have 00, so any date you have will automatically be 1 day into the month. If you don't have day, then cats('01',your_date) then increment that.

Your code formatted for others to use:

Data one; 
  input V U; 
  informat V U date9.; 
  format V U date9.;
  infile datalines missover; 
datalines; 
15feb2012 1jan201215
feb2012 14jan201215
feb2012 15jan201215
apr2012 15jan2012
; 
run; 

data one; 
  set one; 
  duration = max(int((V-U)/(365.25/12)+0.5),1); 
run; 

Data want; 
  input V U duration; 
  informat V U date9. duration; 
  format V U date9.; 
  infile datalines missover; 
datalines; 
15feb2012 1jan2012 2
15feb2012 14jan2012 2
15feb2012 15jan2012 1
15apr2012 15jan2012 3
; 
run;

Re: How to round Interval date

Astounding — Thu, 13 Jul 2017 16:15:40 GMT

If you are happy otherwise with your formula, switch from the MAX function to the CEIL function.

Adding to an incomplete answer here ...

CEIL would have to replace both MAX and INT (not just replace MAX ... INT gets removed).

Be wary of using INTCK unless you understand what it does. Some examples:

30Jan2017 to 02Feb2017 is 1 month

02Jan2017 to 31Jan2017 is 0 months

Additional considerations: How many months are in this interval?

28Jan2017 to 28Feb2017

Re: How to round Interval date

Reeza — Thu, 13 Jul 2017 15:18:52 GMT

@adil256 wrote:

Hi everone,

I would like to round up the month when 1 day or more have elapsed during the month (ex: 3months and 1 day should be considered as 4months; 2 months and 5 days = 3 months; 11 months and 0 days should be 11 months).

This if flawed logic. Look at your third record. You have days in both months, February and January but you show that you want 1 month duration. You also are using the average number of days in a month but are trying to use 'months' as a fixed term when it doesn't have one. To solve this problem you need to clarify your logic first.

Here are a bunch of different ways you could calculate this. Note that I used @RW9 suggestion of moving the date to the beginning/end of the boundarys.

data two;
	set one;
	diff_days = v-u;
	diff_avg_months = (v-u)/(365.25/12);
	diff_months = ceil(diff_avg_months);
	durationC=intck('month', intnx('month', u, 0, 'b'), intnx('month', v, 0, 'e'), 'C');
	durationD=intck('month', intnx('month', u, 0, 'b'), intnx('month', v, 0, 'e'), 'D');

run;

Re: How to round Interval date

art297 — Thu, 13 Jul 2017 15:22:25 GMT

Here is another way:

data want;
  set one;
  if day(U) lt day(V) then duration=intck('month',U,V)+1;
  else duration=intck('month',U,V);
run;

Art, CEO, AnalystFinder.com

Re: How to round Interval date

adil256 — Thu, 13 Jul 2017 15:47:39 GMT

may excuse me if I expressed myself poorly. The duration variable stands for the number of months elapsed between two dates. the function intck gives me the number of months really elapsed betwen the dates. for example, If 3 months and 17 days have been elapsed the intck function will give 3 months elapsed. What I'm looking for it's to considerer these 3 months and 17 days as 4 months elapsed.

@Astounding thank you but ceil function doesn't work perfectly as well :s; the number of month between 15jan2012 and 15feb2012.

I will think about your way of doing it. Thank u very much. @art297 ,@RW9 and @Reeza.

Re: How to round Interval date

Reeza — Thu, 13 Jul 2017 17:35:03 GMT

That's because a 'month' doesn't have a standard definition. 15Jan to 15th of Feb is 31 days which is more than an average month. Technically thats 1 month + less than one day, so in this case you're rounding down when in the others you're rounding up.

Re: How to round Interval date

adil256 — Fri, 14 Jul 2017 17:20:40 GMT

Hello again;

I thought about this way to get what I want.

Data one; 
 input V U ; 
 informat V U date9. duration; 
 format V U date9.; 
 infile datalines missover; 
datalines; 
15feb2012 1jan2012 
15feb2012 14jan2012
15feb2012 15jan2012
15apr2012 15jan2012
; 
run;
data one;
set one;

month_diff=intck('month',U,V,'C');
b=intnx('month',U,month_diff,'sameday');
days_diff=intck('day',U,b,'C');


if c>0 then month_duration =month_diff+1;
else month_duration=month_diff;
run;