How to write conditional where statement in SAS Proc SQL?

Question

I have a macro that would be used for multiple conditions.

%macro Average(data=, tablename=, element=, variablename=, time =);
   PROC SQL;
      CREATE TABLE &tablename. AS 
      SELECT ID, AVG(&element.) AS &variablename.
      FROM &data.
      WHERE date_time < &time or date_time > &time + 1 /*first where condition*/
      GROUP BY ID;
   QUIT;
%mend;

/*second where condition*/  WHERE &Lower. < date_time < &Upper.
/*third where condition*/   WHERE &BP > 0 and &SP<100

I want to put all these three where statements together into the sql macro instead of copy the macro three times. But how could I realize it?

Answer 1

Simply use %if %then %else macro condition, with a new parameter here defined whr:

%macro Average(data=, tablename=, element=, variablename=, time =, whr=);
    PROC SQL;
    CREATE TABLE &tablename. AS 
    SELECT ID, AVG(&element.) AS &variablename.
    FROM &data.
    %if &whr=1 %then %do;
    WHERE date_time < &time or date_time > &time + 1 /*first where condition*/
    %end;
    %else %if &whr=2 %then %do;
    WHERE &Lower. < date_time < &Upper.
    %end;
    %else %if &whr=3 %then %do;
    WHERE &BP > 0 and &SP<100
    %end;
    %else %put 'NO WHERE SPECIFIED';
    GROUP BY ID;
    QUIT;
    %mend;

If the parameter declaration you specify whr=1, it will be the default value. Using %if %then %else you can also use different condition internal to the macro, I mean if you wanna use the first where statement if some condition is true you can specify them.