topic unresolved reference to table in New SAS User

unresolved reference to table

Sandeep77 — Mon, 20 Jun 2022 15:06:19 GMT

Hi Experts,

I am adding current balance details in the table but getting unresolved errors.

I have checked the accountkey in table a and b are numeric. Can you please suggest what's causing the errors?

proc sql;
create table adding_accountkey as 
select a.*,
b.accountkey
from work.stamped_result as a
left join dwhdw.dim_account as b on a.debt_code =b.accountnumber 
order by debt_code;
quit;
(Till here it works perfect. The errors come in the second code)


proc sql;
  create table Current_balance as
  select 
a.*,
b.CurrentBalance
  from work.adding_accountkey as a 

        left join dwhdw.fact_snapshot_accountstatus as a
        on a.accountkey = b.accountkey      ;
quit;

Error in the log:
30         proc sql;
31           create table Current_balance as
32           select
33         a.*,
34         b.CurrentBalance
35           from work.adding_accountkey as a
36         
37                 left join dwhdw.fact_snapshot_accountstatus as a
38                 on a.accountkey = b.accountkey
39         
40         ;
ERROR: Column a.accountkey was found in more than one table in the same scope.
ERROR: Unresolved reference to table/correlation name b.
WARNING: Correlation name a identifies more than one table.
ERROR: Unresolved reference to table/correlation name b.
ERROR: Expression using equals (=) has components that are of different data types.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
41         quit;
NOTE: The SAS System stopped processing this step because of errors.

Re: unresolved reference to table

ballardw — Mon, 20 Jun 2022 15:16:02 GMT

  from work.adding_accountkey as a 

        left join dwhdw.fact_snapshot_accountstatus as a

You used the same alias, A, for both data sets. One of these A have to be B to use b.anyvariablename

Re: unresolved reference to table

Reeza — Mon, 20 Jun 2022 15:16:43 GMT

  from work.adding_accountkey as a 

        left join dwhdw.fact_snapshot_accountstatus as a

You've aliased both tables as A.

Change one to B.

@Sandeep77 wrote:

Hi Experts,

I am adding current balance details in the table but getting unresolved errors.

I have checked the accountkey in table a and b are numeric. Can you please suggest what's causing the errors?

proc sql;
create table adding_accountkey as 
select a.*,
b.accountkey
from work.stamped_result as a
left join dwhdw.dim_account as b on a.debt_code =b.accountnumber 
order by debt_code;
quit;
(Till here it works perfect. The errors come in the second code)


proc sql;
  create table Current_balance as
  select 
a.*,
b.CurrentBalance
  from work.adding_accountkey as a 

        left join dwhdw.fact_snapshot_accountstatus as a
        on a.accountkey = b.accountkey      ;
quit;

Error in the log:
30         proc sql;
31           create table Current_balance as
32           select
33         a.*,
34         b.CurrentBalance
35           from work.adding_accountkey as a
36         
37                 left join dwhdw.fact_snapshot_accountstatus as a
38                 on a.accountkey = b.accountkey
39         
40         ;
ERROR: Column a.accountkey was found in more than one table in the same scope.
ERROR: Unresolved reference to table/correlation name b.
WARNING: Correlation name a identifies more than one table.
ERROR: Unresolved reference to table/correlation name b.
ERROR: Expression using equals (=) has components that are of different data types.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
41         quit;
NOTE: The SAS System stopped processing this step because of errors.