I'm looking to create append the columns of 4 tables essentially based on one similar column. Each column has 2 fields: Group and Value. Here is an example of two tables and what I want the final table to look like:
Table A Table B
Group
Value
Group
Value
Grp1
1
Grp1
2
Grp2
2
Grp3
5
Grp3
3
And I want the final table to look like this:
Table Want
Group
ValueA
ValueB
Grp1
1
2
Grp2
2
0
Grp3
3
5
PROC SQL;
CREATE TABLE WORK.TABLE WANT AS
SELECT t1.GROUP,
COALESCE(t1.VALUE, 0) AS VALUE1,
COALESCE(t2.VALUE, 0) AS VALUE2,
COALESCE(t3.VALUE, 0) AS VALUE3,
COALESCE(t4.VALUE, 0) AS VALUE4
FROM WORK.TABLEA t1, WORK.TABLEB t2, WORK.TABLEC t3, WORK.TABLED t4
WHERE t1.GROUP = t2.GROUP AND t1.GROUP = t3.GROUP AND t1.GROUP = t4.GROUP
ORDER BY t1.GROUP;
QUIT;
The problem appears when one table does have a group so it gets left off completely due to the where statement. Is there a way to solve this so no group gets left off and if it doesn't have a value just make it 0? Thanks in advance!
... View more