understanding concepts of EXCEPT, ALL, CORR in Proc SQL

[KyleS83]

Suppose I have this for my data and program

data a;
input x y;
cards;
1 10
1 10
2 200
3 30
;
run;

data b;
input x z ;
cards;
1 100
2 200
2 200
4 400
;
run;

proc sql;
select * from a
except all corr
select * from b;
quit;


the results will be X with values 1 and 3. My question is how come the 1 will show up and only once if ALL and CORR are used.

[Howles]

See SQL Set Operators: So Handy Venn You Need Them (http://howles.com/saspapers/242-31/242-31.pdf).

[Ksharp]

Based on my knowledge.
All means keep all the duplicated observation and corresponding means matched with the same column.
So once you use all ,then sql will keep duplicated obs, there is only one "1 10" deleted.If you remove all or delete one of "1 10" ,then you will get what you want.


Ksharp

[adibhatl018]

Using the Keyword CORR with the EXCEPT Operator

To display both of the following

• only columns that have the samename
• all uniquerows in the first table that do not appear in the second table
• These are the outputs you generate with various combinations

EXCEPT  Except ALL    EXCEPT all corr

x   y            x   y                x

1  10           1  10               1

3  30           1  10               3

                  3  30