BookmarkSubscribeRSS Feed
littlestone
Fluorite | Level 6
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?
5 REPLIES 5
Ksharp
Super User
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 Employee
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
Fluorite | Level 6
Thank you all for help. It turns out that ISedgwick's method works best.
Again thank you very much.
DBailey
Lapis Lazuli | Level 10
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
Tourmaline | Level 20
Same answer, the one that you define first in you select clause.

/Linus
Data never sleeps

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 751 views
  • 0 likes
  • 5 in conversation