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.
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.
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)".
@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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.