Search code examples
sasproc-sql

Union tables with different columns

I have two large tables (~1GB each) with many different columns on which I want to perform a union all in sas.

Currently, I use the following method with proc sql and union all.

SELECT A, B, '' as C from Table_1 UNION ALL SELECT '' as A, B, C from Table_2

However, this is not preferable as I have dozens of rows in both tables and I am constantly adding to them. Therefore, I am looking for a way to automatically create the blank columns without having to explicitly write them out.

I also tried the following query:
select * from (select * from Table_1), (select * from Table_2)

However, this seems very computationally intensive and takes forever to run.

Are there any better ways to do this? I am also open to using data set instead of proc sql;

Solution

  • A simple data step should do a thing:

    data result_tab;
set Table_1 Table_2;
run;

    This will rewrite both tables. Records from Table_2 will be added at the end of the result_tab. Set statement in data step will declare variables from both input tables.