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'
SELECT * FROM n
WHERE x IN (SELECT * FROM n
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.
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.
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.