Proc SQL convert HEX to character

[sunflower88]

Any suggestion for this question?

PROC SQL;

Connect to odbc(authdomain='sql_auth' datasrc='SQLA');
CREATE TABLE MY.test AS  
SELECT * FROM CONNECTION TO odbc(

select
 A.Location_Id,
 A.Location_Name   
   
from  Location A
Where Active = 1

);
QUIT;

Result -

Location_Id	Location_Name
	FL
	OH
	IL
	HI
	CO

How can I see the value of Location ID which is stored in Hex format?

[tomrvincent]

this should help.

https://communities.sas.com/t5/Base-SAS-Programming/How-to-convert-hexadecimal-to-character/td-p/259...

[sunflower88]

Any solution for PROC SQL instead of data step?

[tomrvincent]

put(v1_hex||v2_hex, $hex8.) as

v1v2_char

[sunflower88]

Thank you for your help!

However,  it  doesn't work for me......  😞

[SuryaKiran]

Hi,

Which database are you connecting to? Since your using Pass-Through query use DBMS specific function to convert the values before reading them into SAS.

Eg: CONVERT(INT,0xFFFFF) in Microsoft SQL Server

[sunflower88]

Us odbc connect to SQL database

Thanks for the help!

[SuryaKiran · Accepted solution]

Try this and see results in SAS

PROC SQL;

Connect to odbc(authdomain='sql_auth' datasrc='SQLA');
CREATE TABLE MY.test AS  
SELECT * FROM CONNECTION TO odbc(

select
 CONVERT(INT,A.Location_Id) as Location_Id,
 A.Location_Name   
   
from  Location A
Where Active = 1

);
QUIT;

[sunflower88]

I got someting back, but I'm expecting something like 0x000000000000001B, instead of 53, 54, 57.

Any idea?

[SuryaKiran]

Convert the Hexa to Char in database. Something like VARCHAR(MAX) instead of INT

[sunflower88]

It works! Thank you for your help!! 🙂