topic Re: ERROR: Ambiguous reference, in SAS Programming

ERROR: Ambiguous reference,

LMSSAS — Wed, 06 Apr 2022 18:04:56 GMT

hello, I am getting the following errors on the "apps_per_agent" table when I run my below proc sql. Can someone offer a suggestion on how to solve for this?

ERROR: Ambiguous reference, column TOH_AGENCY is in more than one table.
ERROR: Ambiguous reference, column 'LEVEL_DOWN 2'n is in more than one table.
ERROR: Ambiguous reference, column 'Level 3'n is in more than one table.
ERROR: Ambiguous reference, column AOR_CODE is in more than one table.

/*Provides count of RTS agents based on TOH*/

proc sql; create table RTS2022_Agents as
select distinct
TOH_AGENCY,
'LEVEL_DOWN 2'N,
'Level 3'n,
AOR_CODE,
count(distinct AGENT_INDIVIDUAL_WRITING_NUMBER) as RTS_Count,
'RTS2022_Agent_Count' as index
From TOH_Data
where Role in ('Agent')
and CERTIFICATION_STATUS IN ('READY TO SELL 2022')
group by
TOH_AGENCY,'LEVEL_DOWN 2'N, 'Level 3'n, AOR_CODE,
/*having*/
calculated index
;
quit;
/*Provides count of active policies based on TOH*/

proc sql; create table Active_Count as
select distinct 
TOH_AGENCY,
count(distinct ins_plcy_id) as Active_Policies,
'Level_Down 2'n,
'Level 3'n,
aor_code
from Data_Active2 a
where cova_evnt_efcv_dt between '01Jan2022'd and today()
group by
TOH_AGENCY,'LEVEL_DOWN 2'N, 'Level 3'n, AOR_CODE;
quit;

/*Provides apps written per agent based on TOH by dividing active policies by the count of RTS agents*/

proc sql; create table Apps_Per_Agent as
select distinct 
a.TOH_Agency,
a.'LEVEL_DOWN 2'N,
a.'Level 3'n,
a.AOR_CODE,
a.RTS_Count,
b.Active_Policies,
round (Active_policies / RTS_Count,0.01) as AppsPerAgent
from RTS2022_Agents a
      inner join 
        Active_Count b
   on a.TOH_AGENCY=b.TOH_AGENCY
group by
TOH_AGENCY,'LEVEL_DOWN 2'N,'Level 3'n, AOR_CODE;
quit;

Re: ERROR: Ambiguous reference,

PaigeMiller — Wed, 06 Apr 2022 18:53:13 GMT

From now on, when you get errors in the LOG, do not show us the errors detached from the code, as you have done. Please show us the ENTIRE log of the PROC SQL that has the errors. Copy as text the ENTIRE log for the PROC SQL that has the errors (every single line for this PROC) and paste it into the window that appears when you click on the </> icon.

Your problem is here:

group by
TOH_AGENCY,'LEVEL_DOWN 2'N,'Level 3'n, AOR_CODE;
quit;

PROC SQL doesn't know if TOH_AGENCY is the one in data set indexed by A or in the data set indexed by B.

Re: ERROR: Ambiguous reference,

ballardw — Wed, 06 Apr 2022 18:56:12 GMT

Please do not separate the error messages from the code. When you do that it makes it much harder to diagnose such things. Copy from the log the Proc SQL code and the error message, on the forum open a text box using the </> and paste all of the copied text.

That error message will appear with the code as shown below:

58   proc sql;
59      create table junk as
60      select name, a.sex
61      from sashelp.class as a
62           left join
63           sashelp.class as b
64           on a.name=b.name
65      ;
ERROR: Ambiguous reference, column name is in more than one table.
66   quit;

It means that you are using two or more data set with the same named variable and you did not specify by using an alias which specific set contributes the value you want to use.

See the highlighted bit below, none of the variables show the table alias.

proc sql; create table Apps_Per_Agent as
select distinct 
a.TOH_Agency,
a.'LEVEL_DOWN 2'N,
a.'Level 3'n,
a.AOR_CODE,
a.RTS_Count,
b.Active_Policies,
round (Active_policies / RTS_Count,0.01) as AppsPerAgent
from RTS2022_Agents a
      inner join 
        Active_Count b
   on a.TOH_AGENCY=b.TOH_AGENCY
group by
TOH_AGENCY,'LEVEL_DOWN 2'N,'Level 3'n, AOR_CODE;
quit;

Re: ERROR: Ambiguous reference,

LMSSAS — Wed, 06 Apr 2022 21:15:30 GMT

Thank you!!