Ambiguous reference, column product_type is in more than one table

lankamanoja — Thu, 20 Oct 2022 12:51:52 GMT

proc sql noprint;
create table
current as
select market, product_type, chains, facility_name , facility_mpin , coalesce(member_status_flag,new_eff_flag) as Flag, count
from
(
select market,product_type, chains, facility_name , facility_mpin , member_status_flag,
count(member_status_flag) as count

from datloc.FIN360_MBR_ROSTER_RPT_Curr
group by market, product_type, chains, facility_name, facility_mpin , member_status_flag

outer union corr

select market, product_type, chains, facility_name , facility_mpin,new_eff_flag,
count(new_eff_flag) as count
from datloc.FIN360_MBR_ROSTER_RPT_Curr
where new_eff_flag is not missing
group by market, product_type, chains, facility_name, facility_mpin ,new_eff_flag
)
order by market, product_type, chains, facility_name , facility_mpin
;
quit;

Log:

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24
25 GOPTIONS ACCESSIBLE;
26 proc sql noprint;
27 create table
28 current as
29 select market, product_type, chains, facility_name , facility_mpin , coalesce(member_status_flag,new_eff_flag) as Flag,
29 ! count
30 from
31 (
32 select market,product_type, chains, facility_name , facility_mpin , member_status_flag,
33 count(member_status_flag) as count
34
35 from datloc.FIN360_MBR_ROSTER_RPT_Curr
36 group by market, product_type, chains, facility_name, facility_mpin , member_status_flag
37
38 outer union corr
39
40 select market, product_type, chains, facility_name , facility_mpin,new_eff_flag,
41 count(new_eff_flag) as count
42 from datloc.FIN360_MBR_ROSTER_RPT_Curr
43 where new_eff_flag is not missing
44 group by market, product_type, chains, facility_name, facility_mpin ,new_eff_flag
45 )
46 order by market, product_type, chains, facility_name , facility_mpin
47 ;
ERROR: Ambiguous reference, column product_type is in more than one table.
ERROR: Ambiguous reference, column chains is in more than one table.
ERROR: Ambiguous reference, column product_type is in more than one table.
ERROR: Ambiguous reference, column chains is in more than one table.
WARNING: Column named product_type is duplicated in a select expression (or a view). Explicit references to it will be to the first
one.
WARNING: Column named chains is duplicated in a select expression (or a view). Explicit references to it will be to the first one.
ERROR: The following columns were not found in the contributing tables: member_status_flag, new_eff_flag.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

Re: Statement is not valid or it is used out of proper order.

PaigeMiller — Thu, 20 Oct 2022 11:58:58 GMT

Whenever you get an error in the log (that's 100% of the time), you need to show us the ENTIRE log for this DATA step or PROC. Please copy the ENTIRE log for this DATA step or PROC and then paste it into the window that appears when you click on the </> icon.

Additional comment:

data datloc.FIN360_MBR_ROSTER_RPT_Curr;
set datloc.FIN360_MBR_ROSTER_RPT_Curr;

it is usually considered to be a poor programming practice to overwrite your data set with a new data set of the exact same name, this erases the original data set (which you might need to go back to at some point). Better would be this:

data datloc.FIN360_MBR_ROSTER_RPT_Curr;
set FIN360_MBR_ROSTER_RPT_Curr2;

topic Ambiguous reference, column product_type is in more than one table in SAS Programming

Ambiguous reference, column product_type is in more than one table

Re: Statement is not valid or it is used out of proper order.