Required Operator not found in Expression: Macro variable in Then statement

[phaneendrap1]

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".

[arthurcavila]

Did you try %then %do; (...); %end; instead of just %then?

[phaneendrap1]

I tried below code, yet same error "Required Operator not found in Expression"

%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 
				%do; 
					MONTH&i_SALES=trx&i;
				%end;
			%ELSE %IF FF = 'HAS' and LENGTH(GEO_ID) = 6 and S_TYPE = ('ETR_DDD') %THEN 
				%do;
					MONTH&i_SALES= UNIT&i;
				%end;
		%end;
	RUN;
%MEND LOOP_I;

[arthurcavila]

It looks like your IF statement should be part of the DATA step program. Try this.

%MACRO LOOP_I;
	DATA JAV_WORK2;
		set WORK.JAV_WORK1;
		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 %by 1;
				MONTH&i_SALES=trx&i;
			%end;
                END;
	        ELSE IF ((FF = 'HAS') and (LENGTH(GEO_ID) = 6) and (S_TYPE = ('ETR_DDD')) THEN DO;
		        %do i = 1 %to 24 %by 1;
				MONTH&i_SALES= UNIT&i;
			%end;
		END;
	RUN;
%MEND LOOP_I;

[DavePrinsloo]

I think you are confusing macro statements and data step statements. If you are not experienced in writing macros, you should always use
OPTIONS MPRINT;
to see what code is being generated. As another tip, write the code without a macro first. (You dont have to write 24 lines, just 3 will do fro a start)
Dont try writing a macro until that works!
Then replace repetitive code and with macro statements, and use OPTIONS MPRINT to see that you are generating the same code that worked without a macro.

[ballardw · Accepted solution]

Very likely no macro is needed. From the code you show I have to assume that you have variables TRX1 to TRX24 and Unit1 to Unit24.

Parallel processing of many similar variables/values is what Arrays are designed to do. The array statement identifies a "short hand" name to associate multiple variables with.

In the code below Array t trx1-trx24; associates a short hand T that by providing and INDEX number, T[1] for instance, you are referencing the variable Trx1. Similar with units.

I am assuming that you do not already have a month_sales variable. The array statement will create new variables when given a stem of a name and the number of elements you need. So Array Month_sales{24} creates 24 variables named Month_sales1 to Month_sales24. With experience you will find that placing an iterated value such as the &i you attempted in the middle of a variable is harder to work with. SAS supports a number of list methods to get relatedly named variables, as in the Trx1 - Trx24, that work much better with the iterator at the end.

If you do already have variables named Month1_sales to Month24_sales, you would typically have to list all of the variables IN ORDER on the array statement to keep the correct TRX and Unit variables aligned for the assignment. There are other list syntax approaches but you might find that Month2_sales comes after Month11_sales because of the way names get ordered.

 DATA JAV_WORK2;
     set WORK.JAV_WORK1;
     array t trx1-trx24;
     array u unit1-unit24;
     array month_sales{24};
     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_SALES[i] = t[i];
         ELSE IF FF = 'HAS' and LENGTH(GEO_ID) = 6 and S_TYPE = ('ETR_DDD') THEN MONTH_SALES[i] = U[i];
     end;
 RUN;

I suspect that if you got past which particular syntax error that stopped the compile that you would also get an error of an undefined macro variable.

The bit you use

MONTH&i_SALES

 Is actually going to look for a macro variable named i_sales . If you want to generate the text "Month1_sales" the macro code would be Month&i._sales  The dot terminates the macro variable use.