CODEWITHSUNDEEP
sqlsasunion
Eyal Marom
Eyal Marom

Reputation: 301

how to perform a UNION where some tables are empty

I wish to perform a UNION between 3 tables, where I know that 1 of them has no values (empty table). I get an ERROR using SAS PROC SQL: ERROR: Table WORK.B doesn't have any columns.

My code:

select *
from work.A
union 
select *
from work.B
union 
select *
from work.C

To my understanding, I need to use EXIST, in order to UNION only existing tables.

Upvotes: 0

Views: 884

Answers (1)

Richard
Richard

Reputation: 27508

The log ERROR message also states PROC SQL requires each of its tables to have at least 1 column.

You can stack (union) your data with a DATA step SET statement and then SORT away duplicates.

Example:

data x;
  set sashelp.class (where=(name=:'A'));

data y;
  stop;

data z;
  retain age weight name height;
  set sashelp.class (where=(name in: ('A', 'J')));
run;

data want;
  set x y z;
run;

proc sort data=want nodupkey;
  by _all_;
run;

Upvotes: 0

Related Questions