topic Re: PROC SQL ORDER BY following a LEFT JOIN Ambiguous reference error in SAS Programming

PROC SQL ORDER BY following a LEFT JOIN Ambiguous reference error

RL997 — Fri, 10 May 2019 16:55:33 GMT

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.

Re: PROC SQL ORDER BY following a LEFT JOIN Ambiguous reference error

PGStats — Fri, 10 May 2019 19:14:42 GMT

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

Re: PROC SQL ORDER BY following a LEFT JOIN Ambiguous reference error

RL997 — Fri, 10 May 2019 20:06:51 GMT

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.

Re: PROC SQL ORDER BY following a LEFT JOIN Ambiguous reference error

ballardw — Fri, 10 May 2019 20:15:14 GMT

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

Re: PROC SQL ORDER BY following a LEFT JOIN Ambiguous reference error

Ksharp — Sat, 11 May 2019 12:34:16 GMT

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

Re: PROC SQL ORDER BY following a LEFT JOIN Ambiguous reference error

Reeza — Sat, 11 May 2019 18:35:21 GMT

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