Writing a macro to take month end's date on 2nd of every month in table CASA_MONTH_END_31JAN23.

[Discaboota]

Hey Guys,

I have tables by the name : CASA_MONTH_END_31JAN23.

I want to make a macro where the Date should change to the current month on the 2nd of every month.

e.g. If the current month is February and today is Feb 2nd then the date should be changed to 28FEB23 and table will be CASA_MONTH_END_28FEB23. this table will be used till 1st March, then on 2nd March it should Change to March.

Please Help.
Thank you.

[PaigeMiller]

Perhaps you mean you want a macro variable and not a macro? (Macro variable and macro are different things, you should not confuse them or refer to macro variables as macros)

data _null_;
    if day(today())=1 then month=intnx('month',today(),-1,'e');
    else month=intnx('month',today(),0,'e');
    call symputx('month',put(month,date7.));
run;
%put &=month;

Additional suggestion: appending month names in the format 28FEB23 to data set names are generally poor choices, as these data sets will not sort chronologically, and it requires people to correctly identify the last day of the month (which isn't hard to do, but could still be a cause of errors). Better would be to name the data sets with months in the format 2023M02, which would sort properly, and would not require people to identify the last day of the month in order to use the data set. You could then use the format YYMM7. instead of DATE7.

[Discaboota]

Hey, Thank you so much for the reply. I have a question, isn't this gonna change the date only on the specified date and not for the rest of the month? If I put this in my code, it is gonna change on the 1st day of the month but in my case, the 2nd day of the month. But for the 1st day, it is gonna take the current month's date. Can you please help me with it?

[Kurt_Bremser]

Just look at @PaigeMiller 's code. The THEN branch will be executed on the first day of a month, the ELSE on all other days. The code works for all days of a month.

[PaigeMiller]

---

@Discaboota wrote:

Hey, Thank you so much for the reply. I have a question, isn't this gonna change the date only on the specified date and not for the rest of the month?

---

The best way to get an answer to this question is to actually try the code and see what it does.

[Kurt_Bremser]

When you date-name files (and this includes datasets), you should always use a YMD date, like 20230131. This makes subsequent handling, like archiving or simply deleting, much easier, and the files sort chronologically on their own.

[Quentin]

If you do want a macro, and not just a macro variable, @PaigeMiller's logic can be translated into a function style macro, e.g.

%macro monthend() ;
  %local monthend ;
  %if %sysfunc(day(%sysfunc(today())))=1 %then %let monthend=%sysfunc(intnx(month,%sysfunc(today()),-1,e),date7) ;
  %else %let monthend=%sysfunc(intnx(month,%sysfunc(today()),0,e),date7) ;
  &monthend
%mend monthend ;

%put CASA_MONTH_END_%monthend() ;

[Discaboota]

Hey, Thank you so much for the reply. I have two questions,
1- Isn't this gonna change the date only on the specified date and not for the rest of the month?
2- If I put this in my code, it is gonna change on the 1st day of the month but in my case, the 2nd day of the month. But for the 1st day, it is gonna take the current month's date. Can you please help me with it?

[Kurt_Bremser]

Nothing is changed in the code. The return value is set, depending on the day-in-a-month of today's date. As I said in my other post, this code will also work on all days of a month.

[Quentin]

On the first day of the month, it returns the last day of the prior month.

On every other day of the month, it returns the last day of the current month.

If you use the macro approach, you can make the code testable by adding a parameter for the value of today, e.g.:

%macro monthend(today=%sysfunc(today())) ;
  %local monthend ;
  %if %sysfunc(day(&today))=1 %then %let monthend=%sysfunc(intnx(month,&today,-1,e),date7) ;
  %else %let monthend=%sysfunc(intnx(month,&today,0,e),date7) ;
  &monthend
%mend monthend ;

You can then test dates like as below:

9    %put CASA_MONTH_END_%monthend() ;
CASA_MONTH_END_28FEB23

10   %put CASA_MONTH_END_%monthend(today="01Jan2023"d) ;
CASA_MONTH_END_31DEC22

11   %put CASA_MONTH_END_%monthend(today="02Jan2023"d) ;
CASA_MONTH_END_31JAN23

12   %put CASA_MONTH_END_%monthend(today="03Jan2023"d) ;
CASA_MONTH_END_31JAN23

13   %put CASA_MONTH_END_%monthend(today="31Jan2023"d) ;
CASA_MONTH_END_31JAN23

14   %put CASA_MONTH_END_%monthend(today="01Feb2023"d) ;
CASA_MONTH_END_31JAN23

15   %put CASA_MONTH_END_%monthend(today="02Feb2023"d) ;
CASA_MONTH_END_28FEB23