BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DougHold
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
DougHold
Obsidian | Level 7

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;

 

View solution in original post

3 REPLIES 3
Patrick
Opal | Level 21

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

Patrick_0-1620440895307.png

 

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
Super User Tom
Super User

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
Obsidian | Level 7

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;

 

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 607 views
  • 0 likes
  • 3 in conversation