topic Re: Change dates to nearest month end in SAS Programming

Change dates to nearest month end

anweinbe — Wed, 20 May 2020 00:10:19 GMT

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!

Re: Change dates to nearest month end

SASKiwi — Wed, 20 May 2020 00:45:56 GMT

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;

Re: Change dates to nearest month end

Reeza — Wed, 20 May 2020 03:26:05 GMT

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!

Re: Change dates to nearest month end

s_lassen — Wed, 20 May 2020 09:59:18 GMT

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;

Re: Change dates to nearest month end

anweinbe — Wed, 20 May 2020 11:29:43 GMT

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