different data type error (SQL)

[kempfz]

I have several blocks of code that are similar that do not produce any errors, however the following block produces the error "Expression using equals (=) has components that are of different data types." I have checked the data types using proc contents and the data types are the same. Can someone help me with this?

proc sql;
	create table work.dcn_missing as 
	select a.che_child_id as Child_ID,
		a.che_child_firstname as Child_First_Name,
		a.che_child_lastname as Child_Last_Name,
		a.redcap_data_access_group as Agency,
		a.che_dob as DOB,
		a.che_enr_date as Child_Enrollment_Date,
		coalesce(c.che_dcn,a.che_dcn) as DCN
	from rdbs.child_enrollment as a 
	left join rdbs.verify_children as b on a.che_child_id = b.child_id
	left join rdbs.bthlink as c on a.che_child_id = c.che_child_id
	where current_status = 'Active' and (a.che_dcn = " " or a.che_dcn < '10000000' ) 
	and a.che_child_id not in (select ID from ignore.'Ignore$'n where Sheet = DCN_Missing);
quit;

[Reeza]

I have checked the data types using proc contents and the data types are the same. Can someone help me with this?

They're different. 

Check every variable in your WHERE statement as well as check that the following is true based on your code below.

• che_dcn is character
• current_status is character
• che_child_id is same as ID from Excel - likely the source of error is here.

a.che_child_id not in (select ID from ignore.'Ignore$'n where Sheet = DCN_Missing);

---

@kempfz wrote:

I have several blocks of code that are similar that do not produce any errors, however the following block produces the error "Expression using equals (=) has components that are of different data types." I have checked the data types using proc contents and the data types are the same. Can someone help me with this?

proc sql;
	create table work.dcn_missing as 
	select a.che_child_id as Child_ID,
		a.che_child_firstname as Child_First_Name,
		a.che_child_lastname as Child_Last_Name,
		a.redcap_data_access_group as Agency,
		a.che_dob as DOB,
		a.che_enr_date as Child_Enrollment_Date,
		coalesce(c.che_dcn,a.che_dcn) as DCN
	from rdbs.child_enrollment as a 
	left join rdbs.verify_children as b on a.che_child_id = b.child_id
	left join rdbs.bthlink as c on a.che_child_id = c.che_child_id
	where current_status = 'Active' and (a.che_dcn = " " or a.che_dcn < '10000000' ) 
	and a.che_child_id not in (select ID from ignore.'Ignore$'n where Sheet = DCN_Missing);
quit;

---