ERROR: Columns could not be found in the table/view identified with the correlation name B.

[mith123]

proc sql;
create table laps_full as
select A.*, B.ref_desc

from laps As A
left join reason_ref as B
on (B.ref_code = A.rsn_cd
and A.src_app_cd = B.src_type_code)
;
quit;

can someone please help me understand why I get the error. I checked the view with alias B and all the columns are present in that view.

[Reeza]

The SAS log isn't usually wrong with that regard. Can you show a proc contents output from each table?

Makes sure to look at the names and labels. And the actual log. 

proc contents data=laps;
run;

proc contents data=reason_ref;
run;

---

@mith123 wrote:

proc sql;
create table laps_full as
select A.*, B.ref_desc

from laps As A
left join reason_ref as B
on (B.ref_code = A.rsn_cd
and A.src_app_cd = B.src_type_code)
;
quit;

can someone please help me understand why I get the error. I checked the view with alias B and all the columns are present in that view.

---

---

@mith123 wrote:

proc sql;
create table laps_full as
select A.*, B.ref_desc

from laps As A
left join reason_ref as B
on (B.ref_code = A.rsn_cd
and A.src_app_cd = B.src_type_code)
;
quit;

can someone please help me understand why I get the error. I checked the view with alias B and all the columns are present in that view.

---

[Kurt_Bremser]

How did you "check the view"? Did you use it as source in a data step, or open it in a table viewer?

The view code itself is not a measure. You can successfully define a SQL view although variables are not present in the source table(s).

You need to check all tables used in the view.

[Sasuser2024]

when you use proc contents and look at the table you will see that SAS has label names and variable names separately but linked. So, you might be using the label name that caused the error. 

[Tom]

Columns?  Do you mean variables? 

• Do the variables with a NAME value of ref_desc, ref_code and src_type_code exist in the dataset referenced as B?  Run PROC CONTENTS and check.

View?  Is the dataset referenced a B a VIEW? 

• What is the definition of the view?  Perhaps the datasets that the view queries have changed so that one or more of those three variables no longer exist or have different names.
• Is it a view into some foreign database?  Does that database use case sensitive variable naming (examples include postgresql and Redshift).