topic Convert date to the monthend date in SAS Programming

Convert date to the monthend date

Sharepoint007 — Thu, 29 Sep 2016 10:06:00 GMT

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.

Re: Convert date to the monthend date

LinusH — Thu, 29 Sep 2016 10:13:55 GMT

Intnx() function solves this.

Re: Convert date to the monthend date

Kurt_Bremser — Thu, 29 Sep 2016 10:28:55 GMT

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.

Re: Convert date to the monthend date

titus — Thu, 29 Sep 2016 13:23:15 GMT

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

Re: Convert date to the monthend date

Reeza — Thu, 29 Sep 2016 19:59:23 GMT

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