Change dates to nearest month end

[anweinbe]

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!

[SASKiwi · Accepted solution]

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;

[Reeza]

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!

---

[s_lassen]

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;

[anweinbe]

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