BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LMSSAS
Quartz | Level 8

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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;

 

 

View solution in original post

3 REPLIES 3
PaigeMiller
Diamond | Level 26

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.

 

2021-11-26 08_27_29-Reply to Message - SAS Support Communities — Mozilla Firefox.png

 

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.

--
Paige Miller
LMSSAS
Quartz | Level 8
Thank you!!
ballardw
Super User

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;

 

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 2368 views
  • 0 likes
  • 3 in conversation