I have a column of dates. Is there a way to make a new field that returns the closest month end?
For example,
Make 3/27/2020 -> 3/31/2020
Make 1/28/2017 -> 1/31/2017
etc.
My date field is currently a "date 9" format if that makes a difference!
On the assumption you are going forward to the nearest month-end:
data test;
My_Date = '27Mar2020'd;
Month_End = intnx('MONTH', My_Date , 0, 'END');
format My_Date Month_End date9.;
put _all_;
run;
On the assumption you are going forward to the nearest month-end:
data test;
My_Date = '27Mar2020'd;
Month_End = intnx('MONTH', My_Date , 0, 'END');
format My_Date Month_End date9.;
put _all_;
run;
What do you want for 3/14/2020 or 2/15/2020 (leap month!), in terms of nearest date?
IMO, using @SASKiwi suggestion of end of the current month is a good approach but different than what you seem to be requesting.
@anweinbe wrote:
I have a column of dates. Is there a way to make a new field that returns the closest month end?
For example,
Make 3/27/2020 -> 3/31/2020
Make 1/28/2017 -> 1/31/2017
etc.
My date field is currently a "date 9" format if that makes a difference!
If I understand your requirement correctly, you want to map e.g. February 3rd into January 31st. How about this:
data have;
format date date9.;
input date date9.;
cards;
03JAN2012
23DEC2019
15JAN2013
;run;
data want;
set have;
format date_eom date9.;
date_eom=intnx('month',date,0,'E');
if date_eom-date>day(date) then
date_eom=intnx('month',date,-1,'E');
run;
I believe that I will always be going forward not backwards. I should have specified that. In any case I will save this too! I appreciate your response :-).
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.