Appending columns when values are missing

[mhoward2]

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!

[Reeza]

Have you tried changing your join type from a cross join to left joins?

[mhoward2]

So: tableA left join tableB on (group = group)
But what if tableA doesnt have a group that tableB has? Wont a left join leave that one out?

[Reeza]

---

@mhoward2 wrote:
So: tableA left join tableB on (group = group)
But what if tableA doesnt have a group that tableB has? Wont a left join leave that one out?

---

Then sounds like you want a full join, isn't it?

[Reeza]

Personally, I'm lazy and that won't scale if you have more tables or more variables. I'm using the table name as the VALUE_tableName but I think if your remove the ID statement it will number them. Depends on if you want to identify the source or are just ordering I suppose.

data long;
set table1 table2 table3 table4 indsname=source;
table = source;
run;

proc transpose data=long out=wide prefix=VALUE_;
by group;
id table;
var Value;
run;

*add zeroes in;
proc stdize data=wide out=want reponly missing=0;
var VALUE_:;
run;

---

@mhoward2 wrote:

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!

---

[mhoward2]

Thank you very much for the reply! Im still learning a lot haha

[PeterClemmensen · Accepted solution]

data A;       
input Group $ Value;
datalines;
Grp1 1
Grp2 2
Grp3 3
;

data B;       
input Group $ Value;
datalines;
Grp1 2
Grp3 5
;

data want;
   if _N_ = 1 then do;
      dcl hash h (dataset : 'B');
      h.definekey('Group');
      h.definedata('Value');
      h.definedone();
   end;

   set A;

   if h.find() then Value = 0;
run;

[mhoward2]

This is great! Thank you so much, worked perfectly and its simple. Really appreciate it