Help using Base SAS procedures

proc sql question

Reply
Frequent Contributor
Posts: 89

proc sql question

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?
Super User
Posts: 9,671

Re: proc sql question

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
SAS Employee
Posts: 2

Re: proc sql question

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, *
Frequent Contributor
Posts: 89

Re: proc sql question

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

Re: proc sql question

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

You have main.key, minor.key and the newly calculated key.
Super User
Posts: 5,255

Re: proc sql question

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

/Linus
Data never sleeps
Ask a Question
Discussion stats
  • 5 replies
  • 131 views
  • 0 likes
  • 5 in conversation