Error when using a Macro variable in DO loop in SAS: Required operator not found in expression

Question

I'm using SAS and I need to combine a number of tables, each of which has suffix of month and year in their name. The specific tables to use will be variable depending on user-defined start and end date. To achieve this, I'm trying to use a do loop via a macro to loop through the months/years in the date range and append to the previous table. However, I'm having issues (seemingly to do with it using the macro variable for the start/end year in the loop). I receive the following errors:

ERROR: Required operator not found in expression: &start_year. 
ERROR: The %FROM value of the %DO QUOTE_YEAR loop is invalid.
ERROR: Required operator not found in expression: &end_year. 
ERROR: The %TO value of the %DO QUOTE_YEAR loop is invalid.
ERROR: The macro GET_PRICES will stop executing.

Here is some example test code I've come up with that replicates the issue which produced the errors above which I am trying to debug. Note for this example, I'm only looping through the years. (I will add the months in once I resolve this issue.)

DATA _NULL_;
    FORMAT start_date end_date DATE9.;
    start_date = '01JUL2018'd;
    end_date = '30JUN2019'd;
    CALL SYMPUT('start_date',start_date);
    CALL SYMPUT('end_date',end_date);
RUN;

%MACRO get_prices(start_date, end_date);
    %LET start_year = year(&start_date.);
    %LET end_year = year(&end_date.);
    %LET start_month = month(&start_date.);
    %LET end_month = month(&end_date.);
    DATA test;
        t = 0;
    RUN;
    %DO quote_year = &start_year. %TO &end_year.;
        DATA test2;
            t = &quote_year.;
        RUN;
        PROC APPEND BASE= test DATA= test2;
    %END;
%MEND;

%get_prices(&start_date.,&end_date.);

The expected output is a table with a single column 't', with 3 rows: (0, 2018, 2019). (The 0 value I just included to initialise a non-empty table on which to append.) The code works when I replace the macro variables to the start/end year in the loop values to their actual value.

Doesn't Work

%DO quote_year = &start_year. %TO &end_year.;

Works

%DO quote_year = 2018 %TO 2019;

I can't work out what is causing this to fail. I believe it must have something to do with the way I've defined the macro variables, but the strange thing is if I remove the do loop completely and have the following data step under the %LET statements, the values appear as expected.

DATA test_macro_values;
    s = &start_year.;
    t = &end_year.;
    u = &start_month.;
    v = &end_month.;
RUN;

Can anyone see what's going wrong?

Alexey Sigida Alexey Sigida · Accepted Answer · 2020-02-11T12:47:37

There are no macro functions called year and month. You should use %sysfunc:

%LET start_year = %sysfunc(year(&start_date.));
%LET end_year = %sysfunc(year(&end_date.));
%LET start_month = %sysfunc(month(&start_date.));
%LET end_month = %sysfunc(month(&end_date.));

Error when using a Macro variable in DO loop in SAS: Required operator not found in expression

1 Answers