BookmarkSubscribeRSS Feed
adil256
Quartz | Level 8

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.

 

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
adil256
Quartz | Level 8

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.

 

Reeza
Super User

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. 

 

 

 

 

Astounding
PROC Star

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

Reeza
Super User

@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;

 

art297
Opal | Level 21

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

 

adil256
Quartz | Level 8

 

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;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1719 views
  • 4 likes
  • 5 in conversation