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.
What do you mean by "So my question is is there any way to get the whole column in Hive SQL please"?
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.