Help using Base SAS procedures

sql tables and ambiguous reference error message

Reply
Frequent Contributor
Posts: 104

sql tables and ambiguous reference error message

Sas users,

Can you please recommend a way to fix the belwo code?  I'm currently receiving the error message; "Ambiguous reference, column new_ID is in more than one table."

In a nutshell I would like to create a new dataset "want" based on dataset "have_1" except I don't want to include in "want" any that are also in "have_2".  The variable in common is "new_ID".  There are duplicate "new_IDs" in "have_1" which may be causing the error message.

proc sql;

create table want as

select new_ID from have_1

except

select new_ID from have_2;

quit;

Thanks.

Super Contributor
Posts: 349

Re: sql tables and ambiguous reference error message

Hi,

data one;

input no;

cards;

1

2

3

;

run;

data two;

input no;

cards;

3

4

5

;

run;

data want;

merge one(in=a) two(in=b);

by no;

if a ne b;

run;

Thanks,

Shiva

Respected Advisor
Posts: 4,644

Re: sql tables and ambiguous reference error message

That query, written as it is, should work, unless new_ID doesn't have the same type or size in both tables, or have_1 or have_2 are views instead of tables, in which case the root of the problem could be inside one of those views.

PG

PG
Super User
Posts: 10,497

Re: sql tables and ambiguous reference error message

Try:

select distinct new_id from have_1

Regular Contributor
Posts: 233

Re: sql tables and ambiguous reference error message

Working fine for me even with duplicates.

data have_1;
input new_id name $1. ;
cards;
1 a
2 b
3 c
4 d
1 a
1 b
run;

data have_2;
input new_id;
cards;
5
6
7
8
9
;
run;

proc sql;
create table want as
select new_ID from have_1
except
select new_ID from have_2;
quit;

Log:

1455  data have_1;
1456  input new_id name $1. ;
1457  cards;

NOTE: The data set WORK.HAVE_1 has 6 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


1464  run;
1465
1466  data have_2;
1467  input new_id;
1468  cards;

NOTE: The data set WORK.HAVE_2 has 5 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


1474  ;
1475  run;
1476
1477  proc sql;
1478  create table want as
1479  select new_ID from have_1
1480  except
1481  select new_ID from have_2;
NOTE: Table WORK.WANT created, with 4 rows and 1 columns.

1482  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

Ask a Question
Discussion stats
  • 4 replies
  • 5211 views
  • 3 likes
  • 5 in conversation