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.
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
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.
@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.
@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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.