BookmarkSubscribeRSS Feed
Nick347
Calcite | Level 5

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.

 

 

4 REPLIES 4
Tom
Super User Tom
Super User

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.

 

Nick347
Calcite | Level 5

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?

Tom
Super User Tom
Super User

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.

Nick347
Calcite | Level 5

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

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 679 views
  • 0 likes
  • 2 in conversation