topic Re: understanding concepts of EXCEPT, ALL, CORR in Proc SQL in SAS Procedures

understanding concepts of EXCEPT, ALL, CORR in Proc SQL

KyleS83 — Sun, 05 Jun 2011 14:11:52 GMT

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.

Re: understanding concepts of EXCEPT, ALL, CORR in Proc SQL

Howles — Sun, 05 Jun 2011 15:00:45 GMT

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

Re: understanding concepts of EXCEPT, ALL, CORR in Proc SQL

Ksharp — Tue, 07 Jun 2011 01:37:00 GMT

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

Re: understanding concepts of EXCEPT, ALL, CORR in Proc SQL

adibhatl018 — Sat, 08 Jun 2013 21:05:30 GMT

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