BookmarkSubscribeRSS Feed
Sharepoint007
Calcite | Level 5

How would I be able to change any date to a monthend date where the dd character is altered to the end of the month dd.

4 REPLIES 4
LinusH
Tourmaline | Level 20
Intnx() function solves this.
Data never sleeps
titus
Obsidian | Level 7

If this is something you will be using often you may want to consider creating a user defined function to access it in the future. Here's an example of my version of this formula and a similar one that returns the First of the Month from either a date or a datetime value.

 

	proc fcmp outlib = dmudfs.UDF.UDF;

		function FirstOfMonth(dt);
		/* 	Purpose: Return the first day of month from either a Date or DateTime argument*/
			if dt/86400 > 1 then FOM = intnx('day', datepart(dt), -day(datepart(dt)) + 1);
			else FOM = intnx('day', dt, -day(dt) + 1);
		return(FOM);
		endsub;

		function LastOfMonth(dt);
		/* 	Purpose: Return the last day of month from either a Date or DateTime argument*/
			if dt/86400 > 1 then LOM = intnx('day',intnx('month', intnx('day', datepart(dt), -day(datepart(dt)) + 1),1),-1);
			else LOM = intnx('day',intnx('month', intnx('day', dt, -day(dt) + 1),1),-1);
		return(LOM);
		endsub;

	quit;

 

The dt/86400>1 as an indicator of date or datetime has served well thus far but there may be a more bulletproof approach.

 

To use the function include this line in your project, making sure to replace "dmudfs" to suit your setup:

options cmplib = dmudfs.UDF;

Then you can call the function by using "LastOfMonth(mydate)".

 

Reeza
Super User

@titus Idea of custom function isn't bad, but I would 0 and the alignment parameter as in the documentation example.

 

	proc fcmp outlib = dmudfs.UDF.UDF;

		function FirstOfMonth(dt);
		/* 	Purpose: Return the first day of month from either a Date or DateTime argument*/
			if dt/86400 > 1 then FOM = intnx('day', datepart(dt), 0, 'b');
			else FOM = intnx('day', dt, 0, 'b');
		return(FOM);
		endsub;

		function LastOfMonth(dt);
		/* 	Purpose: Return the last day of month from either a Date or DateTime argument*/
			if dt/86400 > 1 then LOM = intnx('day', datepart(dt), 0, 'e');
			else LOM = intnx('day', dt, 0, 'e');
		return(LOM);
		endsub;

	quit;

 

 

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 2812 views
  • 2 likes
  • 5 in conversation