CODEWITHSUNDEEP
sasproc-sql
Chickenpet
Chickenpet

Reputation: 17

SAS EG Proc SQL - Adding Brackets to WHERE

In a piece of code I'm working with, I have a PROC SQL step written as so:

PROC SQL;
SELECT
*
FROM
Dataset
WHERE variable = .;
QUIT;

Since a recent upgrade to a newer version of SAS, this code has started causing an issue, specifically with the WHERE command (is it called a command?).

We've discovered a fix is to rewrite the code as so:

PROC SQL;
SELECT
*
FROM
Dataset
(WHERE= (variable = .));
QUIT;

Unfortunately, none of us are quite sure why this makes a difference compared to the version without brackets, so my question is why does this work? Is this a difference in how this is read in an SQL context, or a difference in how SAS treats it and what's the logic behind it?

Thanks.

Upvotes: 0

Views: 499

Answers (1)

Stu Sztukowski
Stu Sztukowski

Reputation: 12849

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;

Upvotes: 1

Related Questions