topic Re: ERROR: Ambiguous reference, column wing_ is in more than one table. in SAS Programming

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

kajal_30 — Sun, 10 Feb 2019 07:32:59 GMT

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

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

Kurt_Bremser — Sun, 10 Feb 2019 07:57:33 GMT

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.

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

kajal_30 — Sun, 10 Feb 2019 08:03:57 GMT

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

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

SASKiwi — Sun, 10 Feb 2019 08:22:10 GMT

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_

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

kajal_30 — Sun, 10 Feb 2019 08:24:21 GMT

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

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

Kurt_Bremser — Sun, 10 Feb 2019 09:27:58 GMT

@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.

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

Patrick — Sun, 10 Feb 2019 09:36:53 GMT

@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.