CODEWITHSUNDEEP
sasproc-sql
J_Lard
J_Lard

Reputation: 1103

proc sql union with different variables

I'm trying to concatenate two tables using a proc sql - union where certain variables are unique to each table. Is there a way to do this without using a NULL placeholder variable? Basically the equivalent of the following data step.

data total;
set t1 t2;
run;

A simple example of what I'm trying to do is shown below.

data animal;
input common $ Animal $ Number;
    datalines;
a Ant 5
b Bird .
c Cat 17
d Dog 9
e Eagle .
f Frog 76
;
run;


data plant;
input Common $ Plant $ Number;
    datalines;
g Grape 69
h Hazelnut 55
i Indigo .
j Jicama 14
k Kale 4
l Lentil 88
;
run;

proc sql;
(select animal.*, '' as plant from animal)
union all corresponding
(select plant.*, '' as animal from plant)
;
quit;

I'd like to be able to run the proc sql with having to create the plant and animal variables in the select statement.

Upvotes: 2

Views: 4360

Answers (1)

Joe
Joe

Reputation: 63424

You want outer union, not union all. That does what you expect (keeps all variables in either dataset). See Howard Schreier's excellent paper on SQL set theory for more information.

proc sql;
create table test as
select * from animal
outer union corr
select * from plant
;
quit;

Upvotes: 3

Related Questions