baikafei baikafei - 5 months ago 22
SQL Question

How to write conditional where statement in SAS Proc SQL?

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

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.