ERROR: Ambiguous reference, column wing_ is in more than one table.

[kajal_30]

Hi Channel,

I have around 20 tables and joining them using (proc sql union all) and they have 40 columns in common and the log shows only error for one column "ERROR: Ambiguous reference, column wing_ is in more than one table."  

can you please advice 

Thanks

[Kurt_Bremser]

If it's just a series of select * union all, you're better off with a data step where you put all 20 datasets into one set statement, and the log from that might tell us more.

[kajal_30]

Hi Thanks for reaching out I cannot change that to datastep because I need to convert this step to a pearl sql step. I am curious to see why only one column causing issue as there are 40 more columns in common

[SASKiwi]

If the column is in more than one table then you need to prefix it by either the table name or table alias you wish to select the column from like so:

MyTable.wing_

/* or */

TableAlias.wing_ 

[kajal_30]

thanks for reply but why I a not seeing the same for rest of the columns as there are 40 more columns in common. This is the only one shown up as an error

[Kurt_Bremser]

---

@kajal_30 wrote:
Hi Thanks for reaching out I cannot change that to datastep because I need to convert this step to a pearl sql step. I am curious to see why only one column causing issue as there are 40 more columns in common

---

I need the result (log) of the datastep for TESTING.

[Patrick]

@kajal_30 

It appears this is a slightly misleading error message.

I can replicate the error message when using a SQL UNION having variables in the SELECT listed where at least for one of the tables the variable doesn't actually exist in the source.

data a b(drop=wing_);
  set sashelp.class;
  wing_='1';
run;

proc sql;
  create table test as
  select name,wing_ from a
  union 
  select name,wing_ from b
  ;
quit;

ERROR: Ambiguous reference, column wing_ is in more than one table.