How to convert column from hexadecimal to character? Error "components are of different data type"

[DougHold]

I have a table with an ID column (ID_td). When I try to query, I get the error "expression using equals has components that are of different data types".

proc sql;
SELECT ID_td
FROM mytable
WHERE ID_td = 'myqueryID'
;
quit;

I checked with proc contents, I see that the column is hexadecimal :

Length: 6

Format: HEX8.

Informat: 9

I think I need to convert from hexadecimal to character? How would I do this?

Thanks

[Patrick]

Proc Contents also tells you of what data type a variable is. With SAS it's either numeric or character.

In your query you've got:  WHERE ID_td = 'myqueryID'

And SAS tells you: "expression using equals has components that are of different data types"

'myqueryID' is a character string but variable ID_td is of type numeric and that's what the error message tells you.

A format only instructs SAS how to print a values. It has no impact on the actually stored value and doesn't come into play in an expression like your where condition which always will use the internal value.

If you want to print your variable using another format just do something like:

proc print data=mytable(obs=3);

format ID_td 32.;

variable ID_td;

run;

[Tom]

You left out the most important part of the CONTENTS informat.  What the TYPE of the variable is.

But we can tell from the FORMAT that is attached that it is a NUMERIC variable and NOT a character varaible.

So it could never have a value like 

'myqueryID'

which is a string of letters.

If you want to type a hex constant start with the digits 0 and end it with the letter X.

WHERE ID_td = 0ABCDEF12x ;

Which is the same thing as writing.

where id_td = 2882400018 ;

because the base 16 number 0ABCDEF12 is the same as the decimal number 2,882,400,018.

[DougHold · Accepted solution]

Thank you Patrick and Tom for the reply, that will be important to keep in mind.

I did check again, and the variable ID_td is indeed type character.

I was able to resolve my issue by inserting a new ID character column (ID_td2):

Data mytable;

Set mytable;

ID_td2 = put(ID_td, $hex8.);

Run;