topic Re: Appending columns when values are missing in SAS Programming

Appending columns when values are missing

mhoward2 — Tue, 24 Nov 2020 20:43:11 GMT

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!

Re: Appending columns when values are missing

Reeza — Tue, 24 Nov 2020 20:56:18 GMT

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

Re: Appending columns when values are missing

mhoward2 — Tue, 24 Nov 2020 20:59:17 GMT

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?

Re: Appending columns when values are missing

Reeza — Tue, 24 Nov 2020 21:01:56 GMT

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!

Re: Appending columns when values are missing

Reeza — Tue, 24 Nov 2020 21:02:39 GMT

@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?

Re: Appending columns when values are missing

PeterClemmensen — Tue, 24 Nov 2020 21:06:41 GMT

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;

Re: Appending columns when values are missing

mhoward2 — Tue, 24 Nov 2020 21:16:15 GMT

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

Re: Appending columns when values are missing

mhoward2 — Tue, 24 Nov 2020 21:16:47 GMT

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