topic Re: PROC SQL UNION in SAS Programming

PROC SQL UNION - unique vs with duplicates

FP12 — Wed, 12 Sep 2018 18:12:27 GMT

Hi,

I have two tables A and B, both with the same columns:

Z1 character7
Z2 character2
Z3 numeric8
Z4 Numeric8
Z5 character200
Z6 numeric8

In A there are 1 121 776 rows

In B there are 114 028 rows.

I do this in a PROC SQL:

		CREATE TABLE &output_table. AS
			SELECT * FROM A
			UNION SELECT * FROM B;

My output table has... 333 456 rows...

Can someone explain me how it is possible???

Thanks by advance

Re: PROC SQL UNION

ChrisBrooks — Wed, 12 Sep 2018 18:11:51 GMT

By default an SQL UNION only selects distinct values. If you want duplicates (i.e all rows from both tables) you need a UNION ALL.

Example from doc:

proc sql;
   title 'A UNION ALL B';
   select * from sql.a
   union all
   select * from sql.b;

Re: PROC SQL UNION

mkeintz — Mon, 02 Oct 2017 15:26:26 GMT

You have duplicates in both data sets. The example below produces one observation even though A has 3 and B has 2:

data a b;
  length z1 $7 z2 $2 z3 z4 8 z5 $200 z6 8;
z1='1234567';
z2='12';
z3=1;
z4=2;
z5=repeat('abcde',40);
z6=3;

output a;output a; output a;
output b;output b;
run;

proc sql;
CREATE TABLE _table AS
			SELECT * FROM A
			UNION SELECT * FROM B;
quit;

Re: PROC SQL UNION

Reeza — Mon, 02 Oct 2017 16:37:23 GMT

UNION doesn't insert duplicate rows.

UNION ALL does insert duplicate rows.

UNION

The UNION operator produces a table that contains all the unique rows that result from both table expressions

Re: PROC SQL UNION

ballardw — Tue, 03 Oct 2017 15:59:26 GMT

data &output_table;

set a b;

run;

Which may run faster than the SQL.