Help using Base SAS procedures

understanding concepts of EXCEPT, ALL, CORR in Proc SQL

Reply
Occasional Contributor
Posts: 9

understanding concepts of EXCEPT, ALL, CORR in Proc SQL

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.
Regular Contributor
Posts: 184

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

See SQL Set Operators: So Handy Venn You Need Them (http://howles.com/saspapers/242-31/242-31.pdf).
Super User
Posts: 10,020

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

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
N/A
Posts: 1

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

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

Ask a Question
Discussion stats
  • 3 replies
  • 4485 views
  • 0 likes
  • 4 in conversation