02-28-2016 02:08 PM
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.
02-28-2016 02:21 PM
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).
02-28-2016 02:35 PM
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.
02-28-2016 03:16 PM
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...
02-28-2016 06:19 PM
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.
02-29-2016 11:35 AM
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!!!!!
02-29-2016 12:06 AM
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...
Need further help from the community? Please ask a new question.