BookmarkSubscribeRSS Feed
ahmed_00
Fluorite | Level 6

I am trying to create table from four tables by using UNION logic, but all the tables have one column different. 

PROC SQL;

create table output_table

select cl1, cl2, cl3 from  tb1

OUTER UNION CORR

select cl1, cl2, cl4 from tbl2

OUTER UNION CORR

select cl1,cl2, cl5 from tbl3

OUTER UNION CORR

select cl1, cl2, cl6 from tbl4;

quit;

on this query the output table contains all columns (cl1,cl2,cl3,cl4,cl5,cl6). 

So my question is is there any way to get the whole column in Hive SQL please. 

 

create table output_table

select cl1, cl2, cl3 from  tb1

UNION ALL

select cl1, cl2, cl4 from tbl2

UNION ALL

select cl1,cl2, cl5 from tbl3

UNION ALL

select cl1, cl2, cl6 from tbl4;

 

IF i use like this the columns on the output table contains only from the first table. (cl1,cl2,cl3)

But I need to get all columns on  my output. 

2 REPLIES 2
mkeintz
PROC Star

What do you mean by "So my question is is there any way to get the whole column in Hive SQL please"? 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Patrick
Opal | Level 21

Can't you just create a common column in your select statement?

  select cl1, cl2, cl3 as cl_common
  from  tb1
  UNION ALL
  select cl1, cl2, cl4 as cl_common
  from tbl2
...and so on

Make sure you use the ALL keyword unless you also want to dedup the result set. 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1192 views
  • 0 likes
  • 3 in conversation