BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Daylon_Hunt
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

View solution in original post

10 REPLIES 10
LinusH
Tourmaline | Level 20

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
Daylon_Hunt
Obsidian | Level 7

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
Tourmaline | Level 20

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
TomKari
Onyx | Level 15

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
Super User

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

TomKari
Onyx | Level 15

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

Daylon_Hunt
Obsidian | Level 7

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
Super User
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
Super User
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
Meteorite | Level 14

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

 

//Contact me to learn how Metacoda software can help keep your SAS platform secure - https://www.metacoda.com

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
  • 10 replies
  • 3076 views
  • 11 likes
  • 5 in conversation