Macro variable to create a dataset with month-end between two dates

[Vinz867]

Hi, I am trying to create a macro that will calculate the month-end dates between 2 dates. This is what I have so far, but it looks like I am missing something. Any help would be greatly appreciated.

%macro MonthEndDataset(start_date, end_date, dataset_name);
data &dataset_name;
format month_end_date date9.;
start_date = input("&start_date", date9.);
end_date = input("&end_date", date9.);
month_end_date = intnx('month', start_date, 0, 'e');

do while(month_end_date <= end_date);
output;
month_end_date = intnx('month', month_end_date, 1, 'e');
end;
run;
%mend MonthEndDataset;

%MonthEndDates('01JAN2024'd, '08AUG2024'd);

Here is the error message: 

ERROR: Invalid DO loop control information, either the INITIAL or TO expression is missing or the BY expression is missing, zero,
or invalid.

[ballardw · Accepted solution]

You macro name is MonthEndDataset.

The macro you attempt to execute is %MonthEndDates.

By any chance did you get an ERROR about a statement is not valid?

And a WARNING about macro MONTHENDDATES not resolved?

If you get any errors in your log you should include your log text pasted into a text box opened on the forum using the </> icon above the message window.

Major issue, you macro parameters attempt to parse a date value. 

start_date = input("&start_date", date9.);

When you provide value of the macro variable start_date as shown in your call the above resolves to

start_date = input("'01JAN2024'd", date9.);

If you test that statement with  such as

2874  data junk;
2875  start_date = input("'01JAN2024'd", date9.);
2876  run;

NOTE: Invalid argument to function INPUT at line 2875 column 14.
start_date=. _ERROR_=1 _N_=1
NOTE: Mathematical operations could not be performed at the following places. The results of the
      operations have been set to missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      1 at 2875:14

Because the first part of the text value the input statement is trying to parse with the date9 informat is the single quote.

You passed a date value if you use '01JAN24'd and should not be parsed. Personally I dislike passing quotes in macro parameters and your  code as written would expect a call more like:

%MonthEndDataset(01JAN2024, 08AUG2024, testdataset);

Which runs successfully as written.

Suggestion: provide a format for the values of Start_date and End_date in the data step so you can more easily evaluate if the output is as desired.

Suggestion: post code by opening a text box using the </>. That will prevent the forum software from reformatting the pasted text. The reformatting may occasionally introduce not normally visible characters that can make code copied from the window not run. Also the boxes visibly separate the code from the discussion.

%macro MonthEndDataset(start_date, end_date, dataset_name);
data &dataset_name;
   format month_end_date date9.;
   start_date = input("&start_date", date9.);
   end_date = input("&end_date", date9.);
   month_end_date = intnx('month', start_date, 0, 'e');

   do while(month_end_date <= end_date);
      output;
      month_end_date = intnx('month', month_end_date, 1, 'e');
   end;
   format start_date end_date date9.;
run;
%mend MonthEndDataset;
%MonthEndDataset(01JAN2024, 08AUG2024, testdataset);

Note that the indentation that I added to the code copied from the message window is kept when pasted into the text box.

Minor issue, you did not provide and output data set name. That will generate datasets with names like Data1, Data2, Data3 etc. if the code executes at all. Not knowing which set is about to be created can make it very hard to debug data results.

[Vinz867]

Thank you so much! I made the updates to the code and it worked. 

[ChrisNZ]

You can simplify your macro:

%macro MonthEndDataset(start_date, end_date, dataset_name);
  data &dataset_name.;
    format MONTH_END_DATE date9.;
    MONTH_END_DATE = intnx('month', "&start_date"d, 0, 'e');
    do while(MONTH_END_DATE <= "&end_date"d);
      output;
      MONTH_END_DATE = intnx('month', MONTH_END_DATE, 1, 'e');
    end;
  run;
%mend MonthEndDataset;

%MonthEndDataset(01JAN2024, 08AUG2024, WANT);

 

[Ksharp]

%macro MonthEndDataset(start_date, end_date, dataset_name);
  data &dataset_name.;
   do date = &start_date. to &end_date.;
      if month(date) ne month(_date) and not missing(_date) then output;
	  _date=date;
   end;
   keep _date;
   format _date date9.;
  run;
%mend MonthEndDataset;

%MonthEndDataset('01JAN2024'd, '08AUG2024'd, WANT);

[PaigeMiller]

---

@Ksharp wrote:

%macro MonthEndDataset(start_date, end_date, dataset_name);
  data &dataset_name.;
   do date = &start_date. to &end_date.;
      if month(date) ne month(_date) and not missing(_date) then output;
	  _date=date;
   end;
   keep _date;
   format _date date9.;
  run;
%mend MonthEndDataset;

%MonthEndDataset('01JAN2024'd, '08AUG2024'd, WANT);

---

The logical error here is that every data set created is named WANT, so each new data set overwrites the previous one.

The other problem with the original question is that it is usually a bad idea to split up data sets like this. Unless there is very strong justification to do this, then I would say don't do this at all.