PROC SQL ORDER BY following a LEFT JOIN Ambiguous reference error

[RL997]

Hello,

I am trying to join tables in SQL and then order by an id common to the new and joined tables.  How do I get rid of the ambiguous reference error?

PROC SQL;
 
 CREATE TABLE new AS
 SELECT A.*, B.*
 FROM table1 as A LEFT JOIN table2 as B
 ON A.id = B.id
 
 ORDER BY A.id, A.xx;

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

QUIT;

Thanks in advance for your help.

[PGStats]

I cannot reproduce this error. There is no name ambiguity in a.id. Your code should generate a warning instead:

17   data t1;
18   set sashelp.class;
19   keep name height;
20   run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.T1 has 19 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


21
22   data t2;
23   set sashelp.class;
24   keep name weight;
25   run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.T2 has 19 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


26
27   proc sql;
28   create table new as
29   select a.*, b.*
30   from t1 as a left join t2 as b on a.name=b.name
31   order by a.name, a.height;
WARNING: Variable Name already exists on file WORK.NEW.
NOTE: Table WORK.NEW created, with 19 rows and 3 columns.

32   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.12 seconds
      cpu time            0.06 seconds

[RL997]

I also had a "where" statement in my code that I didn't think was relevant to my question.  That turned out to be the source of the ambiguous reference error.

Thanks for looking at the problem.

[ballardw]

---

@RL997 wrote:

I also had a "where" statement in my code that I didn't think was relevant to my question.  That turned out to be the source of the ambiguous reference error.

Thanks for looking at the problem.

---

Which is why you will repeated requests to paste the code and any errors from the LOG.

It is not uncommon to "simplify code" and remove the problem part because of an assumption of where a problem occurs. Often the part that SAS complains about is related to something occurring before the detection because of things like mismatched quotes, brackets or missing semicolons.

[Reeza]

---

@RL997 wrote:

I also had a "where" statement in my code that I didn't think was relevant to my question.  That turned out to be the source of the ambiguous reference error.

Thanks for looking at the problem.

---

Please mark the question as solved then. Mark your previous answer with the explanation of why it was resolved.

[Ksharp]

Rename one of ids.

 CREATE TABLE new AS
 SELECT A.*, B.*
 FROM table1 as A LEFT JOIN table2 as B(rename=(id=_id))
 ON A.id = B._id