ERROR: Required operator not found in expression (SAS)

Question

I am trying to go through the loop from the start date and end date as follow:

Error:

Required operator not found in expression: YEAR(INTNX('MONTH', MDY(2,1,2020), 3, 'SAME'))*100+MONTH(INTNX('MONTH', MDY(2,1,2020), 3, 'SAME'))

Code:

%LET ENDING_YEAR         = 2021;   
%LET ENDING_MONTH        = 3;    
%LET STARTING_YEAR         = 2020;
%LET STARTING_MONTH        = 2;

DATA COUNT;
 START_DATE = MDY(&STARTING_MONTH.,1,&STARTING_YEAR.);
 END_DATE =  MDY(&ENDING_MONTH.,1,&ENDING_YEAR.);
 COUNT_MONTH = INTCK('MONTH',START_DATE,END_DATE)+1;
 COUNT_MONTH_4 = COUNT_MONTH - 4;
 COUNT_MONTH_3 = COUNT_MONTH - 3;
 call symputx('NUM_MONTH', COUNT_MONTH);
 call symputx('NUM_MONTH_4', COUNT_MONTH_4);
 call symputx('NUM_MONTH_3', COUNT_MONTH_3);
 RUN;


%MACRO FINAL_DATA;
%DO i = 0 %TO &NUM_MONTH_4.;
    %LET ADDITION = %EVAL(&i.+3);
    %LET TARGET_DB=INTNX('MONTH', MDY(&STARTING_MONTH.,1,&STARTING_YEAR.), &ADDITION., 'SAME');
    %LET TARGET_DB_MONTH = MONTH(&TARGET_DB.);
    %LET TARGET_DB_YEAR = YEAR(&TARGET_DB.);
    %LET DB = %EVAL(&TARGET_DB_YEAR.*100+&TARGET_DB_MONTH.);
    LIBNAME TETRIS "/actuary/Commercial/Monthend/&DB.";
    %LET TARGET_EFF =INTNX('MONTH', MDY(&STARTING_MONTH.,1,&STARTING_YEAR.), &i., 'SAME');
    %LET TARGET_EFF_MONTH = MONTH(&TARGET_EFF.);
    %LET TARGET_EFF_YEAR = YEAR(&TARGET_EFF.);
    %EXTRACTION(INFORCE, &TARGET_EFF_YEAR.,&TARGET_EFF_MONTH.);
%END;   

%DO j = &NUM_MONTH_3. %TO &NUM_MONTH.;
    %LET DB = %EVAL(&ENDING_YEAR.*100+&ENDING_MONTH.);
    LIBNAME TETRIS "/actuary/Commercial/Monthend/&DB.";
    %LET TARGET_EFF =INTNX('MONTH', MDY(&STARTING_MONTH.,1,&STARTING_YEAR.), &j., 'SAME');
    %LET TARGET_EFF_MONTH = MONTH(&TARGET_EFF.);
    %LET TARGET_EFF_YEAR = YEAR(&TARGET_EFF.);  
    %EXTRACTION(INFORCE, &TARGET_EFF_YEAR.,&TARGET_EFF_MONTH.);     
%END;

%MEND;
    
%FINAL_DATA;

I am new to SAS. Any advice would be greatly appreciated.

Thank you.

Answer 1

If you want to call SAS functions like YEAR() or INTNX() in macro logic then each function call needs to use the %SYSFUNC() macro function to invoke the SAS function.

So in this statement:

%LET DB = %EVAL(&TARGET_DB_YEAR.*100+&TARGET_DB_MONTH.);

because the macro variable's being referenced have strings like these:

TARGET_DB_YEAR resolves to YEAR(INTNX('MONTH', MDY(2,1,2020), 3, 'SAME'))
TARGET_DB_MONTH resolves to MONTH(INTNX('MONTH', MDY(2,1,2020), 3, 'SAME'))

instead of actual number of days since 1960 SAS uses to store date values you are asking the %eval() function to do something it does not understand. It can only do simple integer arithmetic and boolean logic.

To loop over a series of months just use an offset from the starting month with INTNX() function. If you want to generate a YYYYMM string of digits just use the YYMMN format.

So you could use something like this:

%let start=%sysfunc(mdy(&starting_month,1,&starting_year));
%let end=%sysfunc(mdy(&ending_month,1,&ending_year));
%do offset = 0 %to %sysfunc(intck(month,&start,&end));
  %let date=%sysfunc(intnx(month,&start,&offset));
  %let db=%sysfunc(putn(&date,yymmn6.));
  ...
%end;

Answer 2

The code is missing the %SYSFUNC() required for using functions in macro logic.

How is SAS supposed to know if should be a text value of 'INTNX' or if you want to use it as a function? To differentiate, everything is interpreted as text unless you specify otherwise. Using %SYSFUNC() tells SAS you want to use a SAS function.

Each function needs to be wrapped in the %SYSFUNC(), so for nested functions you'll need multiple calls.

In addition, when using macro functions you do not include the parameters in quotes, so you need to remove the quotes around the MONTH and SAME parameters.

%DO i = 0 %TO &NUM_MONTH_4.;

    %LET ADDITION = %EVAL(&i.+3);

    %LET TARGET_DB=%sysfunc(INTNX(MONTH, %sysfunc(MDY(&STARTING_MONTH.,1,&STARTING_YEAR.)), &ADDITION., S));

    %LET TARGET_DB_MONTH = %sysfunc(MONTH(&TARGET_DB.));

    %LET TARGET_DB_YEAR = %sysfunc(YEAR(&TARGET_DB.));

    %LET DB = %EVAL(&TARGET_DB_YEAR.*100+&TARGET_DB_MONTH.);

    LIBNAME TETRIS "/actuary/Commercial/Monthend/&DB.";

    %LET TARGET_EFF = %sysfunc(INTNX(MONTH, %sysfunc(MDY(&STARTING_MONTH.,1,&STARTING_YEAR.)), &i., S));

    %LET TARGET_EFF_MONTH = %sysfunc(MONTH(&TARGET_EFF.));

    %LET TARGET_EFF_YEAR = %sysfunc(YEAR(&TARGET_EFF.));
                    
    %PUT _ALL_;

%END;