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 :-).
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: