topic proc sql unexpected result of nested intersection/except in SAS Procedures

proc sql unexpected result of nested intersection/except

Nick347 — Wed, 17 May 2023 21:39:32 GMT

Hello!

I am a newbie in SAS, but faced with unexpected behavior of intersection/except nested in select

Consider 2 datasets m and n

data m;
input x$ @@;
cards;
a b e
;
run;

data n;
input x$ @@;
cards;
a d e
;
run;

The following query with IN gives the expected result 'd'

proc sql;
SELECT * FROM n
WHERE x IN (SELECT * FROM n
            EXCEPT 
           SELECT * FROM m);

Unlike the query with EQUAL instead of IN, which returns 'e'

proc sql;
SELECT * FROM n
WHERE x EQUAL (SELECT * FROM n
            EXCEPT 
           SELECT * FROM m);

Can anybody explain such strange behavior? I would really appreciate your help.

I can give more examples, in case they are needed.

Re: proc sql unexpected result of nested intersection/except

Tom — Wed, 17 May 2023 21:52:08 GMT

How can a single value be equal to a SET?

The real question is why doesn't PROC SQL generate an error when given an impossible query.

Re: proc sql unexpected result of nested intersection/except

Nick347 — Wed, 17 May 2023 22:31:28 GMT

Goes it mean that it is invalid to call SELECT query after EQUAL operator even if the SELECT is going to return a single value?

Re: proc sql unexpected result of nested intersection/except

Tom — Wed, 17 May 2023 22:51:13 GMT

First thing is that there is no EQUAL operator.

It only ran because you can use EQ as a replacement for the = operator and SAS will assume you meant EQ.

31   proc sql;
32   select name,age,age equal 10 as age2
                         -----
                         1
WARNING 1-322: Assuming the symbol EQ was misspelled as equal.

33   from sashelp.class
34   ;

In theory you can use a select in that way, it is just not a very useful tool.

You have to absolutely positive that it will only return one observation. Otherwise you get this error message

103  proc sql;
104  select name, (select age from sashelp.class ) as age
105  from sashelp.class
106  ;
ERROR: Subquery evaluated to more than one row.

And only one variable. Otherwise you get this error message

107  proc sql;
108  select name, (select age,height from sashelp.class where name='Alice' ) as age
109  from sashelp.class
110  ;
ERROR: A Composite expression (usually a subquery) is used incorrectly in an expression.

It does seem that trying to use that particular EXCEPT query in that way does not work properly.

You probably would need to open a ticket with SAS Support to get an explanation of what is going wrong.

Re: proc sql unexpected result of nested intersection/except

Nick347 — Thu, 18 May 2023 05:57:16 GMT

Thank you so much for your help, I will follow your advise regarding a ticket.