proc sql question

[littlestone]

I have two tables which are called Main and Minor, respectively. In table Main, there is a variable called KEY which takes value of 1, 2, 3, 4, 5, 6; In table Minor, there is also a variable called KEY which takes value of 1, 3, 4, 7.

I ran following codes:

proc sql;
create table TEST as
select *,
coalesce (Main.Key, Minor.Key) as Key
from Main full join Minor
on Main.Key = Minor.Key
;
quit;


However, in the table TEST created, the variable Key only takes 1, 2 , 3, 4, 5, 6. My question is: why the Key variable wouldn't take 7?

[Ksharp]

Hi.
How about this:

[pre]
data main;
input key;
cards;
1
2
3
4
5
6
;
run;
data minor;
input _key;
cards;
1
3
4
7
;
run;
proc sql;
create table TEST as
select *,
coalesce (Main.Key, Minor._Key) as __Key
from Main full join Minor
on Main.Key = Minor._Key
;
quit;
[/pre]


Ksharp

[ISedgwick]

SAS creates the variables in the order it sees them. In your case 3 versions of the variable Key are created 2 from the original tables and 1 from the coalesce. The select statement determines the order, so in your case the key that is used is comming from the table Main. If you don't want to change the names try altering the order ie.

select coalesce (Main.Key, Minor.Key) as Key, *

[littlestone]

Thank you all for help. It turns out that ISedgwick's method works best.
Again thank you very much.

[DBailey]

What happens when proc sql has three variables all called the same thing?

You have main.key, minor.key and the newly calculated key.

[LinusH]

Same answer, the one that you define first in you select clause.

/Linus