Column could not be found in the table with the correlation name

[Sandeep77]

Hi Expert,

I am running a code to generate some information but I am getting the below error message. Can you please let me know what should I focus to change in the code?

proc sql;
create table Conflict_Check_Results as
	select distinct t1.*,
					t3.rep_code,
					t2.Cust_Conflict
		from LIMA as t1
			left join StopList_Check3 as t2 on t1.icustomerid = t2.icustomerid
			left join p2scflow.debt as t3 on t1.debt_code = t2.debt_code;
quit;

Error Log:

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Processing Stop List';
4          %LET _CLIENTPROCESSFLOWNAME='LIMA Trace';
5          %LET _CLIENTPROJECTPATH='S:\Trace\Trace Integrity Analyst Data\BAU_Processes\SAS\Enhanced TPT\Storage\Ad Hoc\HCE Trace -
5        ! 27-09-2022\HCE Trace Request (LIMA & TPT).egp';
6          %LET _CLIENTPROJECTPATHHOST='LWLT5CG9322XFL';
7          %LET _CLIENTPROJECTNAME='HCE Trace Request (LIMA & TPT).egp';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=SVG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         %macro HTML5AccessibleGraphSupported;
15             %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
16         %mend;
17         ODS LISTING GPATH=&sasworklocation;
18         FILENAME EGHTML TEMP;
19         ODS HTML5(ID=EGHTML) FILE=EGHTML
20             OPTIONS(BITMAP_MODE='INLINE')
21             %HTML5AccessibleGraphSupported
22             ENCODING='utf-8'
23             STYLE=HTMLBlue
24             NOGTITLE
25             NOGFOOTNOTE
26             GPATH=&sasworklocation
27         ;
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
28         
29         proc sql;
30         create table Conflict_Check_Results as
31         	select distinct t1.*,
32         					t3.rep_code,
33         					t2.Cust_Conflict
34         		from LIMA as t1
35         			left join StopList_Check3 as t2 on t1.icustomerid = t2.icustomerid
36         			left join p2scflow.debt as t3 on t1.debt_code = t2.debt_code;
ERROR: Column debt_code could not be found in the table/view identified with the correlation name T2.
ERROR: Column debt_code could not be found in the table/view identified with the correlation name T2.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
37         quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.09 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              7343.96k
      OS Memory           37732.00k
      Timestamp           09/30/2022 05:39:47 PM
      Step Count                        37  Switch Count  13
      
38         
39         
40         %LET _CLIENTTASKLABEL=;
41         %LET _CLIENTPROCESSFLOWNAME=;
42         %LET _CLIENTPROJECTPATH=;
43         %LET _CLIENTPROJECTPATHHOST=;
2                                                          The SAS System                           14:42 Friday, September 30, 2022

44         %LET _CLIENTPROJECTNAME=;
45         %LET _SASPROGRAMFILE=;
46         %LET _SASPROGRAMFILEHOST=;
47         
48         ;*';*";*/;quit;run;
49         ODS _ALL_ CLOSE;
50         
51         
52         QUIT; RUN;
53         

[PeterClemmensen · Accepted solution]

.The error message tells you that the variable debt_code is not in StopList_Check3.

I suspect that all you need to do is change t2 to t3 in the last join. 

Like this

proc sql;
create table Conflict_Check_Results as
	select distinct t1.*,
					t3.rep_code,
					t2.Cust_Conflict
		from LIMA as t1
			left join StopList_Check3 as t2 on t1.icustomerid = t2.icustomerid
			left join p2scflow.debt as t3 on t1.debt_code = t3.debt_code;
quit;

[Sandeep77]

Thank you 🙂