SAS PROC SQL joins two tables using a FULL JOIN
Suppose I have two datasets,
--Table 1-- --Table 2--
ID Amount ID Amount1 Code
A01 0.1 A01 0.3 x
A02 0.2 A02 0.2 y
A02 0.3 A03 0.4 g
A03 0.4 A03 0.5 u
A05 0.6 B01 0.1 k
I am trying to create a new dataset (Table 3) by joining Table 1 and Table 2. The end result should look like this:
--Table 3--
ID Amount Amount1 Code
A01 0.1 . .
A01 . 0.3 x
A02 0.2 0.2 y
A02 0.3 . .
A03 0.4 0.4 g
A05 0.6 . .
B01 . 0.1 k
where the table will be concatenated based on id with sum and count of 1 being compared at the same time. I tried to use PROC SQL FULL JOIN but the results seem a little strange. Thank.
+3
source to share
2 answers
The only thing that might not be obvious to a novice user is the need to do coalesce () on identifiers.
proc sql;
create table joined as
select coalesce(a.ID, b.ID) as ID, a.Amount, b.Amount1, b.Code
from Table1 a
full join Table2 b
on a.ID = b.ID;
quit;
In any case, the SAS way of doing this is to join the two tables. If you have pre-sorted tables or indexes by ID, this will be more efficient as well:
data merged;
merge table1 table2;
by ID;
run;
+4
source to share
The following code gives a result very close to the result you asked, although I couldn't figure out why "u" was set to be missing from the requested dataset.
proc sql;
create table joined as
select coalesce(a.ID, b.ID) as ID, a.Amount, b.Amount, b.Code
from Table1 a natural full join Table2(rename=(amount1=amount)) b
;
quit;
0
source to share