topic Re: proc sql question in SAS Procedures

proc sql question

littlestone — Fri, 11 Mar 2011 05:42:45 GMT

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?

Re: proc sql question

Ksharp — Fri, 11 Mar 2011 08:15:43 GMT

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

Re: proc sql question

ISedgwick — Fri, 11 Mar 2011 10:41:55 GMT

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, *

Re: proc sql question

DBailey — Fri, 11 Mar 2011 12:54:23 GMT

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

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

Re: proc sql question

LinusH — Fri, 11 Mar 2011 13:03:46 GMT

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

/Linus

Re: proc sql question

littlestone — Fri, 11 Mar 2011 18:55:14 GMT

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