Proc SQL Join Errors, keep all data

[jerrylshen]

Hi, I'm trying to merge/join multiple tables together into, but retaining essentially all the info. I'm using SAS 9 and I'm open to any SAS/Proc SQL advice

Table 1

╔════════════════╦═════════╦═════════╦════════╦═══════════╗
║     status     ║ Message ║ Subject ║ Field1 ║   Forms   ║
╠════════════════╬═════════╬═════════╬════════╬═══════════╣
║       1        ║ aba     ║     001 ║ one    ║ "01 & 02" ║
║       1        ║ bab     ║     002 ║ one    ║ "01 & 02" ║
╚════════════════╩═════════╩═════════╩════════╩═══════════╝

Table 2

╔════════════════╦═════════╦═════════╦════════╦═══════════╗
║     status     ║ Message ║ Subject ║ Field2 ║   Forms   ║
╠════════════════╬═════════╬═════════╬════════╬═══════════╣
║       1        ║ aaa     ║     001 ║ two    ║ "01 & 02" ║
║       1        ║ vvv     ║     002 ║ two    ║ "01 & 02" ║
╚════════════════╩═════════╩═════════╩════════╩═══════════╝

Expected final output:

╔════════╦══════════╦═════════╦════════╦════════╦═══════════╗
║ status ║ Message  ║ Subject ║ Field1 ║ Field2 ║   Forms   ║
╠════════╬══════════╬═════════╬════════╬════════╬═══════════╣
║      1 ║ aba, aaa ║     001 ║ one    ║ two    ║ "01 & 02" ║
║      1 ║ bab, vvv ║     002 ║ one    ║ two    ║ "01 & 02" ║
╚════════╩══════════╩═════════╩════════╩════════╩═══════════╝

I've tried proc sql full join and right/left join, but my results end up being something like

proc sql;

CREATE TABLE t1 AS
SELECT *
FROM table1 full join table2 on table1.Subject = table2.Subject
            full join table3 on table1.Subject = table3.Subject
;
quit;

Result's status would only be based on table1's status

╔════════════════╦═════════╦═════════╦════════╦════════╦═══════════╗
║     status     ║ Message ║ Subject ║ Field1 ║ Field2 ║   Forms   ║
╠════════════════╬═════════╬═════════╬════════╬════════╬═══════════╣
║       1        ║ aba     ║     001 ║ one    ║        ║ "01 & 02" ║
║       1        ║ bab     ║     002 ║ one    ║        ║ "01 & 02" ║
║       1        ║ aaa     ║     001 ║        ║ two    ║ "01 & 02" ║
║       1        ║ vvv     ║     002 ║        ║ two    ║ "01 & 02" ║
╚════════════════╩═════════╩═════════╩════════╩════════╩═══════════╝

Where am I going wrong with the joins here? Or how can I get the expected output?

Thanks!   

EDIT: fixed the Expected Final Output table. So, Message should concatenate while also merging. This is probably a lot more complicated now:/

[novinosrin]

It appears you want to APPEND rather than JOIN

data want;

set one two;

run;

[jerrylshen]

Shoot sorry, I just edited my question, it's a bit different now where this answer wouldn't work 100%.

[novinosrin]

Hello @jerrylshen 

data one;
infile cards ;
input (status    Message  Subject  Field1    Forms  ) ( &:$10.); 
cards;
 1        aba          001  one     "01 & 02" 
 1        bab          002  one     "01 & 02" 
 ;


data two;
infile cards ;
input (status    Message  Subject  Field2    Forms  ) (& :$10.); 
cards;
1         aaa         001  two     "01 & 02" 
1        vvv          002  two    "01 & 02"
;


data want;
merge one two(rename=message=m);
by status;
message=catx(',',message,m);
drop m;
run;

[novinosrin]

If you want to use PROC SQL

data one;
infile cards ;
input (status    Message  Subject  Field1    Forms  ) ( &:$10.); 
cards;
 1        aba          001  one     "01 & 02" 
 1        bab          002  one     "01 & 02" 
 ;


data two;
infile cards ;
input (status    Message  Subject  Field2    Forms  ) (& :$10.); 
cards;
2         aaa         001  two     "01 & 02" 
2        vvv          002  two    "01 & 02"
;

proc sql;
create table want as
select * from one
outer union corr 
select * from two;
quit;

[Tom]

Why PROC SQL?  Why not just use normal SAS code?

[jerrylshen]

I'm open to either SAS/Proc SQL advices. I'm more familiar with SQL than SAS, but I'm willing to learn the SAS method of doing this