Solved
Contributor
Posts: 20

# Calculating Number of Days Until Month End

I'm trying to create a calculated field in my target table that tells me how many days are between a specified date and the end of the current month.  For example, I have a "Due Date" field that represents when a payment is due and for testing purposes we'll say that date is 3/5/2016.  I want to create a field that represents the number of days that payment will be past due come the end of the month (3/31/2016 - 3/5/2016).  However, I'm not wanting to hard code the end of month date since I want the formula to work for any given month.  Essentially needing a field that is always calculating the number of days between a given "Due Date" and the end of that same month, for any given month.

Accepted Solutions
Solution
‎02-29-2016 11:31 AM
Super User
Posts: 23,700

## Re: Calculating Number of Days Until Month End

Posted in reply to Daylon_Hunt

Month_End = Intnx('month', date, 0, 'e');

All Replies
Super User
Posts: 5,876

## Re: Calculating Number of Days Until Month End

Posted in reply to Daylon_Hunt

The intnx() function can give you any date/time boundary.

To count the no of days, just subtract the calculated month end and your date (and add 1 depending you wish to count the "date" itself).

Data never sleeps
Contributor
Posts: 20

## Re: Calculating Number of Days Until Month End

I think the first step for me is figuring out how to come up with the date for the end of the "current" month.  For example, if today was 3/5/2016, what would the expression be to come up with 3/31/2016 as the date at the end of that month.  Intuitively I thought to use:  LAST_DAY(Date_Due), but it's not working.

Super User
Posts: 5,876

## Re: Calculating Number of Days Until Month End

Posted in reply to Daylon_Hunt

Perhaps I wasn't clear. The intnx() function will give you the last day of the month (and many other things as well).

There is no LAST_DAY function in SAS.

All (almost) functions are listed within DI Studio in the expression builder. And of course in the documentation section of support.sas.com. Even if it can be hard to understand upfront what intnx() means...

Data never sleeps
PROC Star
Posts: 1,307

## Re: Calculating Number of Days Until Month End

Posted in reply to Daylon_Hunt

As @Linus says, read the description of the intnx function, and try using it in some basic programs. If you have problems getting it to work. then post your questions here.

Tom

Solution
‎02-29-2016 11:31 AM
Super User
Posts: 23,700

## Re: Calculating Number of Days Until Month End

Posted in reply to Daylon_Hunt

Month_End = Intnx('month', date, 0, 'e');

PROC Star
Posts: 1,307

## Re: Calculating Number of Days Until Month End

@Reeza PPHHHHHHTTTT! They were supposed to try it themselves!!

Contributor
Posts: 20

## Re: Calculating Number of Days Until Month End

First off, you've all been amazing!  I'm currently in the process of rebuilding our entire Bank's reporting system and I clearly have lots of questions.  To my defense though, the "LAST_DAY" function does appear in my list of available functions, and intuitvely that has been what I've been trying so desperately to make work for this particular situation.  In our current reporting environment (Oracle), I use a similar function for this particular custom field, and I think that's also why I was trying to use that one instead of INTNX.  Either way, THANK YOU ALL!!!!!

Super User
Posts: 23,700

## Re: Calculating Number of Days Until Month End

Posted in reply to Daylon_Hunt
Yeah...I know. I've tried the same many times. Then you'll find the same question on SO, FB and Linkedin Someone usually answers it.
Super User
Posts: 23,700

## Re: Calculating Number of Days Until Month End

Posted in reply to Daylon_Hunt
I think using the 0 in INTNX isn't that intuitive though. I don't think thats covered in docs though I could be mistaken.
Posts: 1,403

## Re: Calculating Number of Days Until Month End

The 0 value for the INTNX increment argument is documented with an example - http://support.sas.com/documentation/cdl/en/lefunctionsref/67960/HTML/default/viewer.htm#p10v3sa3i4k...

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
• 10 replies
• 846 views
• 11 likes
• 5 in conversation