Calculating Number of Days Until Month End

[Daylon_Hunt]

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.

[LinusH]

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).

[Daylon_Hunt]

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.  

[LinusH]

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...

[TomKari]

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

[Reeza · Accepted solution]

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

[TomKari]

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

[Daylon_Hunt]

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!!!!!

[Reeza]

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.

[Reeza]

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.

[MichelleHomes]

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...