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;

 

 

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
  • 4 replies
  • 2001 views
  • 2 likes
  • 5 in conversation