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?

score 3 · Answer 1 · answered May 28 '15 at 06:52

If you want to optionally call different combinations of where conditions you could do something like the below where you set them to default to 1 unless you assign them to an additional where condition:

%macro Average(data=, tablename=, element=, variablename=, time=
              ,whr1=1
              ,whr2=1
              ,whr3=1);

  PROC SQL;
  CREATE TABLE &tablename. AS 
    SELECT ID, AVG(&element.) AS &variablename.
    FROM &data.
    WHERE (&whr1) and (&whr2) and (&whr3)
    GROUP BY ID;
 QUIT;
%mend;

Then you could call the macro with your where conditions eg:

%Average(whr1=%str(date_time < &time or date_time > &time + 1))

%Average(whr1=%str(date_time < &time or date_time > &time + 1)
        ,whr2=%str(&Lower. < date_time < &Upper.)
        ,whr3=%str(WHERE &BP > 0 and &SP<100))

I would prefer this way - make the where condition itself the macro argument. More flexible and easier to read/understand the code. — Joe, May 28 '15 at 14:12

score 0 · Accepted Answer · answered May 28 '15 at 07:31

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.

This works, but I find this much harder to read than just including the WHERE condition as a macro parameter, and harder to maintain. — Joe, May 28 '15 at 14:12

How to write conditional where statement in SAS Proc SQL?

2 Answers2