I have to join two table but it is giving an error

[annypanny]

I have to join two table but it is giving an error: Ambiguous reference, column schemenumber is in more than one table.

I have two table scheme1 and strategies in which there are multiple coloumns, some of the coloumn name have same in both the table.

I am using the following code:

proc sql;
create table lifepath(keep=schemenumber uniquestrategyid uniquefundid)
as select * from scheme1 as scheme1, strategies as strategies
where schemenumber=schemenumber
and defaultstrategy=uniquestrategyid;
quit;

I have to join these two table and keep only three coloumns. How can I do that.

[PeterClemmensen]

Try this

proc sql;
create table lifepath(keep=schemenumber uniquestrategyid uniquefundid)
as select * from scheme1 as scheme1, strategies as strategies
where scheme1.schemenumber=strategies.schemenumber
and defaultstrategy=uniquestrategyid;
quit;

[annypanny]

thanks, now it is running but throwing a warning: Variable SchemeNumber already exists on file WORK.LIFEPATH. How can I get rid of that?

[robulon]

Hi,

The reason this is happening is because when you select *, you are telling sas you want to include everything from both the tables, and as SchemeNumber exists on both tables you can't have two variables with the same name. In order to get rid of it, you would need to tell SAS which of the two tables you want to pull SchemeNumber from.

[annypanny]

what if I have to pull the values of schemenumber from both the tables

[robulon · Accepted solution]

If you need the values of schemenumber from both tables, you would need to do a full join and use the coalesce function (coalesce(a.schemenumber,b.schemenumber) as schemenumber). This would then merge on and schemenumbers that appear in both tables, but would also include any that appear in the second table but not the first.

[annypanny]

thanks it is working for men the answer code is here:

proc sql;
create table lifepath
as select coalesce(scheme1.schemenumber, strategies.schemenumber), uniquestrategyid, uniquefundid  
as schemenumber from scheme1 full join strategies
on scheme1.schemenumber=strategies.schemenumber
and scheme1.defaultstrategy=strategies.uniquestrategyid;
quit;

[Kurt_Bremser]

---

@annypanny wrote:

what if I have to pull the values of schemenumber from both the tables

---

The way your join is built (inner join), that would only give you redundant information. If you want a full join, use

coalesce(s1.schemenumber,st.schemenumber) as schemenumber

in the select.

[Kurt_Bremser]

DO NOT use the asterisk in SQL, especially not when joining tables. ALWAYS use an explicit list of variables, and use aliases to tell SAS which variable from which table to take:

proc sql;
create table lifepath as
select
  s1.schemenumber,
  st.uniquestrategyid,
  st.uniquefundid
from scheme1 as s1, strategies as st
where s1.schemenumber=st.schemenumber
and s1.defaultstrategy=st.uniquestrategyid;
quit;

[novinosrin]

HI @annypanny  Whenever you see an error that states ambiguous reference, that essentially means the SQL processor is unable to  SELECT/pick the column as the columns are read. The SQL processor expects an alias or in other words  a reference that points to the specific table wherein the column exists that needs to be read.

You have used alias/reference in the FROM clause to denote the reference 

from scheme1 as scheme1, strategies as strategies

However, you still need to point the column names with those references as a two level names with those references as a prefix like

scheme1.schemenumber=strategies.schemenumber

The same principle applies to the select clause as well and so the select clause ought to be written as

select scheme1.schemenumber,uniquestrategyid,uniquefundid

Finally the complete construct is bound to have the following syntax

proc sql;
 create table lifepath as
 select scheme1.schemenumber,uniquestrategyid,uniquefundid
 from scheme1 as scheme1, strategies as strategies
 where scheme1.schemenumber=strategies.schemenumber
 and defaultstrategy=uniquestrategyid;
quit;

Please refrain from using * if you are working with production code for the simple reason, leads to ambiguity, confusion and also difficult to maintain should you work with a team of people supporting each other. It's always better to be explicit and clear for you and for your team.