DATA Step, Macro, Functions and more

Calculating Number of Days Until Month End

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

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: 17,784

Re: Calculating Number of Days Until Month End

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

View solution in original post


All Replies
Super User
Posts: 5,256

Re: Calculating Number of Days Until Month End

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,256

Re: Calculating Number of Days Until Month End

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,090

Re: Calculating Number of Days Until Month End

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: 17,784

Re: Calculating Number of Days Until Month End

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

PROC Star
Posts: 1,090

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: 17,784

Re: Calculating Number of Days Until Month End

Yeah...I know. I've tried the same many times. Then you'll find the same question on SO, FB and Linkedin Smiley Happy Someone usually answers it.
Super User
Posts: 17,784

Re: Calculating Number of Days Until Month End

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.
Trusted Advisor
Posts: 1,247

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.

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

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