2

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.

Joe
  • 62,789
  • 6
  • 49
  • 67
J_Lard
  • 1,083
  • 6
  • 18
  • Do note you are not using NULLs but zero length strings (non-null values). – Parfait Mar 08 '17 at 16:32
  • @Parfait These are nulls in SAS insofar as SAS has null. (They're actually *not* zero length strings, they're one length strings, `''` evaluates to `' '` in SAS.) – Joe Mar 08 '17 at 16:38
  • Thanks @Joe for that clarification. – Parfait Mar 08 '17 at 16:39

1 Answers1

3

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;
Joe
  • 62,789
  • 6
  • 49
  • 67
  • Wow... can't believe I missed that. Thanks for the answer. – J_Lard Mar 08 '17 at 16:22
  • Interesting method. Though, AFAIK, the `outer union` is not ANSI SQL but strictly SAS's dialect. So this will not work should you connect to other RDBMS (Oracle, DB2, SQL Server, Postgres, MySQL). – Parfait Mar 08 '17 at 16:37
  • @Parfait Good point. If there's a way to do this in ANSI SQL, it might make a good second answer (as that way should probably also work in SAS; mostly SAS's SQL dialect is ANSI-plus). – Joe Mar 08 '17 at 16:40
  • The OP's original attempt with `UNION ALL` (no `corresponding` clause) would probably be the only way I can see for portable SQL code. – Parfait Mar 08 '17 at 16:42