components that are of different data types

[Sandeep77]

Hi everyone.

I have created a table which has debt_code and icustomerid. Now I am using that table in another code. Debt_code is character and icustomerid is numberic and I think for that reason I am getting error. How can I change icustomerid to character to avoid this issue? Thanks

and t1.debt_code not in (select debt_Code from Stamped_200X_Looped)
and t1.debt_code not in (select debt_Code from Trace_Stamped)
and t2.icustomerid not in (select debt_code from adding_icustomerid)

error log:

59         					and t1.debt_code not in (select debt_Code from LAS_Stamped_Looped)
60         					and t1.debt_code not in (select debt_Code from Stamped_200X_Looped)
61         					and t1.debt_code not in (select debt_Code from Trace_Stamped)
62         					and t2.icustomerid not in (select debt_code from adding_icustomerid)
63         						order by t2.icustomerid;
ERROR: Expression using IN has components that are of different data types.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
64         run;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.

[PaigeMiller]

Debt_code is character and icustomerid is numberic and I think for that reason I am getting error. 

yes, that is probably why you are getting the error message.

This can be fixed by 

and t2.icustomerid not in (select put(debt_code,best12.) from adding_icustomerid)

Of course, without having your data, this fix is UNTESTED.

[Sandeep77]

Thank you for letting me know. Just one quick question. I know that debt_code is my source but not sure what should be the format which you have mentioned best12. Could you please let me know?

[PaigeMiller]

---

@Sandeep77 wrote:
Thank you for letting me know. Just one quick question. I know that debt_code is my source but not sure what should be the format which you have mentioned best12. Could you please let me know?

---

I don't know, it was a guess because I don't have your data. You have to look at the variable values with your own eyes and see what length it has and what values it takes on. This determines the proper format to use.

[ballardw]

How to avoid this:

Make sure that when your data sets are created that variables that are going to be intended for such actions are created as the same data type. Further, if they have the same name or are supposed to hold the same information in different tables, I would suggest making sure that the lengths are the same.

Quite often the underlying cause of different variable types for variables that have the same name is traceable to use of Proc Import or a wizard using Import to bring the data into SAS. Next would be connecting to an external data source and bringing in data of a different type. Something else if not using Proc Import to read external files into SAS is to check the code that is used to read the external files to make sure that all the programs are using the same type and length.

[Tom]

Why would variables named ICUSTOMERID and DEBT_CODE ever have matching values?  Are you sure they should be compared?

If the values can be converted to numbers (you cannot convert strings that contain letters into numbers) then it is easier to compare the numeric value than compare the strings.  That is because there is only one way to represent a number so you don't have to worry about lead/trailing spaces or leading zeros.

and t2.icustomerid not in (select input(debt_code,32.) from adding_icustomerid)