## Convert date to the monthend date

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,917

## Re: Convert date to the monthend date

Intnx() function solves this.
Data never sleeps
Super User
Posts: 10,574

## Re: Convert date to the monthend date

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
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 15

## Re: Convert date to the monthend date

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: 24,010

## 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;``````

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