DATA Step, Macro, Functions and more

How to round Interval date

Reply
Contributor
Posts: 30

How to round Interval date

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.

 

Super User
Super User
Posts: 7,942

Re: How to round Interval date

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;
Contributor
Posts: 30

Re: How to round Interval date

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.

 

Super User
Posts: 19,768

Re: How to round Interval date

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. 

 

 

 

 

Super User
Posts: 5,495

Re: How to round Interval date

[ Edited ]

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

Super User
Posts: 19,768

Re: How to round Interval date


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;

 

PROC Star
Posts: 7,467

Re: How to round Interval date

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

 

Contributor
Posts: 30

Re: How to round Interval date

 

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;

 

Ask a Question
Discussion stats
  • 7 replies
  • 117 views
  • 4 likes
  • 5 in conversation