Ambiguious column refernce in Union All

Question

I am receiving an error message with my union all indicating that member_key is ambiguous. Adding an alias does not seem to resolve this. Any suggestions?

proc sql;
Select distinct member_key
into: member_ky_list separated by ","
From(
select 
member_key 
from work.Member_Admit_icd 
UNION ALL
Select
member_key 
from work.Member_ICD 
UNION ALL
Select
member_key 
from work.member_cpt);
Quit;

Answer 1

It seems to be a misleading error message.

I was able to replicate the error only when at least for one of the table the member_key does not exist in the source tables.

    data member_admit_icd member_icd member_cpt(drop=member_key);
        set sashelp.cars;
        member_key=1;
    run;
    
    proc sql;
        select distinct member_key into: member_ky_list separated by "," 
            from (
            select member_key 
            from work.Member_Admit_icd 
            UNION ALL 
            select member_key 
            from 
            work.Member_ICD 
            UNION ALL 
            select member_key 
            from work.member_cpt
            );
    quit;

ERROR: Ambiguous reference, column member_key is in more than one table.

Please check that the member_key variable is present in every source table.