Required Operator not found in Expression:

Question

In the below code, I'm using macro variables in then statement, however any variation of code seems to be failing in one or the other.

%MACRO LOOP_I;
    DATA JAV_WORK2;
        set WORK.JAV_WORK1;
        %do i = 1 %to 24 %by 1;
            %IF FF in ('CV', 'CV1', 'CV2', 'CVA', 'CVP', 'HAS') and S_TYPE in ('ETR_CARD', 'ETR_PCP', 'ETR_TRX') %THEN MONTH&i_SALES=trx&i;
            %ELSE %IF FF = 'HAS' and LENGTH(GEO_ID) = 6 and S_TYPE = ('ETR_DDD') %THEN MONTH&i_SALES= UNIT&i;
        %end;
    RUN;
%MEND LOOP_I;

%LOOP_I

When I tried removing % from IF statements, then I was receiving "ERROR 180-322: Statement is not valid or it is used out of proper order". TIA

Are you trying to create new macro variables with this logic, or new columns in a data step? It is hard to identify exactly what you are trying to do. If this gets corrected in its current form, it will create 24 new if/then statements with 24 new variables variables all with the same value every time the conditions are true. — Stu Sztukowski
Yes, I intend to create 24 new columns based on existing 24 columns. In this case my macro variable, should create MONTH1_SALES from TRX1 column and similarly for 23 other columns — bzflag

Stu Sztukowski Stu Sztukowski · Accepted Answer · 2020-05-26T19:14:04

You can achieve the same results using arrays instead of macro loops.

data jav_work2;
    set jav_work1;

    array sales_month[24];
    array trx[24];
    array unit[24];

    if(FF  IN('CV', 'CV1', 'CV2', 'CVA', 'CVP', 'HAS') 
       AND S_TYPE in ('ETR_CARD', 'ETR_PCP', 'ETR_TRX')
    then do;

        do i = 1 to 24;
            sales_month[i] = trx[i];
        end;

            else if(FF = 'HAS' AND length(GEO_ID) = 6 AND S_TYPE = 'ETR_DDD') then do; 
               do i = 1 to 24
                  sales_month[i] = unit[i];
               end;
            end;
    end;
run;

If you wanted to use the same naming convention, you can enclose it within a macro and modify the sales_month[24] array statement with this:

array sales_month[24] %do i = 1 %to 24; month&i._sales %end; ;

Required Operator not found in Expression:

1 Answers