topic Vertical SQL combine in New SAS User

Vertical SQL combine

Batman — Mon, 12 Aug 2019 20:06:55 GMT

I'm learning about combining datasets vertically using SQL. I'm trying to understand why the output includes a row where X=2 but no row where X=3. Anyone have any idea?

data one;

input X 1. A $1.;

datalines;

;

data two;

input X 1. B $1.;

datalines;

;

run;

proc sql;

select *

from one

except

select *

from two;

Re: Vertical SQL combine

novinosrin — Mon, 12 Aug 2019 20:40:52 GMT

Hello @Batman

You are basically asking the SQL processor to exclude records that match two and only output the nonmatches from one

Also, since you didn't use ALL i.e except all, you are telling SQL to do a double pass to exclude duplicate records in the nonmatches from one.

HTH

In essence, this is what you are doing

Set Difference
Difference between sets is denoted by ‘A – B’, is the set containing elements of set A but not in B. i.e all elements of A except the element of B.

Above is the Venn Diagram of A-B.

Re: Vertical SQL combine

ballardw — Tue, 13 Aug 2019 14:53:13 GMT

Since your only common variable between the data sets is X that is the only variable considered for the comparison. The variable A will not be compared to B for determining inclusion/exclusion with the Except operation.

~~Name B to A in the set Two and see what happens.~~

BTW, it is not a good idea to start your datalines with a blank unless you really truly actually want to have a row of missing values.

Re: Vertical SQL combine

PGStats — Tue, 13 Aug 2019 04:08:09 GMT

Contrary to what is stated above (sorry @ballardw), SQL set operations without the CORR keyword operate on columns by positions, not by name. So the comparison involves column A against column B. The names are taken from the first table involved. So 2c is kept because it is different from 2y but 3v is removed because it is present in table two.

Try EXCEPT CORR and see what happens.

Re: Vertical SQL combine

ballardw — Tue, 13 Aug 2019 14:52:35 GMT

@PGStats wrote:

Contrary to what is stated above (sorry @ballardw), SQL set operations without the CORR keyword operate on columns by positions, not by name. So the comparison involves column A against column B. The names are taken from the first table involved. So 2c is kept because it is different from 2y but 3v is removed because it is present in table two.

Try EXCEPT CORR and see what happens.

I sit corrected. Thank you.

Re: Vertical SQL combine

Tom — Tue, 13 Aug 2019 15:19:13 GMT

Because '2c' is in one and not in two, but the only value with X=3 in one is '3v' and that value is in two so it is deleted by EXCEPT.

Re: Vertical SQL combine

Batman — Tue, 13 Aug 2019 16:12:01 GMT

Ok, that makes sense, why is the additional value of "1a" also dropped.

Re: Vertical SQL combine

Tom — Tue, 13 Aug 2019 16:18:04 GMT

SQL is a set operation language. In general a "SET" of values does not include duplicate values. So it makes no sense to have two exact duplicates be the result of such a set operation.