DATA Step, Macro, Functions and more

PROC SQL UNION

Reply
Contributor
Posts: 34

PROC SQL UNION

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

Super Contributor
Posts: 440

Re: PROC SQL UNION

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.

Trusted Advisor
Posts: 1,022

Re: PROC SQL UNION

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;
Super User
Posts: 19,822

Re: PROC SQL UNION

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
Super User
Posts: 11,343

Re: PROC SQL UNION

Or

 

data &output_table;

   set a b;

run;

 

Which may run faster than the SQL.

Ask a Question
Discussion stats
  • 4 replies
  • 183 views
  • 2 likes
  • 5 in conversation