When you run your code, you get a warning from the SQL that i is already present in the output dataset.
24 proc sql;
25 create table a_full_join_b as
26 select * from a full join b
27 on a.i=b.i;
WARNING: Variable i existiert bereits in Datei WORK.A_FULL_JOIN_B.
NOTE: Table WORK.A_FULL_JOIN_B created, with 3 rows and 3 columns.
28 run;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
(Don't mind the partly German messages. The localization of SAS can be funny at times.)
This is a consequence of your indiscriminate use of the asterisk in the select, and it also points to your problem:
There can only be one "i" variable in the select, so only the first one encountered is taken; therefore the order in which the datasets are joined becomes important.
Follow Maxim 25 (Have a clean log) and get rid of the warning, and use the proper SQL function to eliminate your problem:
data a;
do i = 1 to 2;
x = "a";
output;
end;
run;
data b;
do i = 2 to 3;
y = "b";
output;
end;
run;
proc sql;
create table a_full_join_b as
select
coalesce(a.i,b.i) as i,
x,
y
from a full join b
on a.i = b.i
;
quit;
proc print data=a_full_join_b;
run;
proc sql;
create table b_full_join_a as
select
coalesce(a.i,b.i) as i,
x,
y
from b full join a
on a.i = b.i
;
quit;
proc print data=b_full_join_a;
run;
Result:
Beob. i x y
1 1 a
2 2 a b
3 3 b
Beob. i x y
1 1 a
2 2 a b
3 3 b
As you can see, the outputs are a) complete and b) identical. And there are no extraneous NOTEs and no WARNINGs in the log.
Note that I also used the correct quit; statement to end the proc SQLs.
... View more
