SAS EG Proc SQL – Adding Brackets to WHERE

There should not be a difference between two versions of SAS. The two pieces of code you posted are identical in function.

The second SQL code you are using is a SAS-specific feature that allows you to apply dataset options during input/output. This is available to nearly all procs in SAS 9.4/Viya, including SQL and the data step. This is helpful for renaming variables, pre-filtering output data from procs, or applying database-specific options.

The input where= option is used less often, but does have a helpful application in SQL that can improve efficiency within SAS. For example, the following two pieces of code are equivalent:

Filtering data before joining using a subquery:

proc sql noprint;
    create table foo as
        select t1.group, sum(t2.var) as sum_var
        from table1 as t1
        LEFT JOIN
        (select * from table2 where var > 2) as t2
        ON t1.group = t2.group
    ;
quit;

Filtering data before joining without using a subquery:

proc sql noprint;
    create table foo as
        select t1.group, sum(t2.var) as sum_var
        from table1 as t1
        LEFT JOIN
        table2(where=(var > 2)) as t2
        ON t1.group = t2.group
    ;
quit;

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top