How to use local macro variable in upper-level nest if statement in SAS

Question

The goal is to use a local macro variable (TOTAL_NUMBER_OF_APPLE) created in Logic A to decide the following if statement.

%macro Exec_day1();
    %if &A. = "APPLE" %then %do;
        %put NOTE: Calculate number of apples in basket; 
        %include "Logic A.sas";
         end;
             %if &TOTAL_NUMBER_OF_APPLE. >= 0  %then %do; 
             %include "Logic B.sas";
             %end;
             %else %if &TOTAL_NUMBER_OF_APPLE. < 0 %then %do;
             %put ERROR;
        %end;
    %end;   
%mend;

%macro Exec_day1();

In logic A.sas I created a local macro variable (TOTAL_NUMBER_OF_APPLE) as

proc sql;
    &conn_to_snowflake.;
    select  TOTAL_NUMBER_OF_APPLE
    into    :TOTAL_NUMBER_OF_APPLE
    from    connection to SF
        (Select Sum(total apples) AS TOTAL_NUMBER_OF_APPLE
        from tableA
        );
quit;

It seems the TOTAL_NUMBER_OF_APPLE is only a local variable saved in Logic A, I can't call it in my upper if statement, following is the error message.

WARNING: Apparent symbolic reference TOTAL_NUMBER_OF_APPLE not resolved.

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: &TOTAL_NUMBER_OF_APPLE. >= 0

Answer 1

The passthru query looks like it is using invalid syntax. You cannot have two variables listed inside the SUM() function call.

Note that if the SELECT statement does not run then PROC SQL will never create the macro variable.

In general in that type of pattern it is best to set the macro variable to some default value before running the SELECT statement.

%let TOTAL_NUMBER_OF_APPLE=0;
select TOTAL_NUMBER_OF_APPLE
  into :TOTAL_NUMBER_OF_APPLE
  from connection to SF
    (select Sum(total_apples) AS TOTAL_NUMBER_OF_APPLE
    from tableA
    )
;