DATA Step, Macro, Functions and more

Convert date to the monthend date

Reply
Occasional Learner
Posts: 1

Convert date to the monthend date

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.

Super User
Posts: 5,424

Re: Convert date to the monthend date

Posted in reply to Sharepoint007
Intnx() function solves this.
Data never sleeps
Super User
Posts: 7,762

Re: Convert date to the monthend date

Posted in reply to Sharepoint007

Comprehensive documentation of the intnx() fuction is found here.

You need to make sure that your dates are stored as SAS date values, of course.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 14

Re: Convert date to the monthend date

Posted in reply to Sharepoint007

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

 

Super User
Posts: 19,771

Re: Convert date to the monthend date

@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;

 

 

Ask a Question
Discussion stats
  • 4 replies
  • 358 views
  • 2 likes
  • 5 in conversation