SAS EG Proc SQL - Adding Brackets to WHERE

Question

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.

Hi Stu. Apologies for the slow response. We're getting a syntax error, "expecting one of the following: a name, etc..." with the SAS log placing the red line beneath the "." at the end of the WHERE statement. — Chickenpet
Very odd. Two checks that I want to do: 1. Can you post some sample data that is not working (preferably in a datalines format or .csv file)? 2. Can you go to the editor and type in %put &sysvlong., check the log and post that value? — Stu Sztukowski
Hi Stu. As it turns out, a colleague has had a similar issue with the code and discovered that, for some reason, the issue actually lies in the Dataset name, which is set by macros. This is now something new for us to look into, as we're learning about the code. Thanks very much for your contribution to the thread. :) — Chickenpet
Ah, macros strike again! It happens sometimes! mlogic is a great tool! — Stu Sztukowski

Stu Sztukowski Stu Sztukowski · Accepted Answer · 2020-11-03T14:32:12

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;

SAS EG Proc SQL - Adding Brackets to WHERE

1 Answers