topic Re: Proc SQL convert HEX to character in SAS Programming

Proc SQL convert HEX to character

sunflower88 — Fri, 23 Feb 2018 17:23:45 GMT

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?

Re: Proc SQL convert HEX to character

tomrvincent — Fri, 23 Feb 2018 19:22:54 GMT

this should help.

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

Re: Proc SQL convert HEX to character

sunflower88 — Fri, 23 Feb 2018 19:42:43 GMT

Any solution for PROC SQL instead of data step?

Re: Proc SQL convert HEX to character

tomrvincent — Fri, 23 Feb 2018 19:53:16 GMT

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

v1v2_char

Re: Proc SQL convert HEX to character

SuryaKiran — Fri, 23 Feb 2018 20:06:15 GMT

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

Re: Proc SQL convert HEX to character

sunflower88 — Fri, 23 Feb 2018 20:22:19 GMT

Us odbc connect to SQL database

Thanks for the help!

Re: Proc SQL convert HEX to character

SuryaKiran — Fri, 23 Feb 2018 20:25:53 GMT

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;

Re: Proc SQL convert HEX to character

sunflower88 — Fri, 23 Feb 2018 20:33:18 GMT

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

Any idea?

Re: Proc SQL convert HEX to character

sunflower88 — Fri, 23 Feb 2018 20:34:39 GMT

Thank you for your help!

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

Re: Proc SQL convert HEX to character

SuryaKiran — Fri, 23 Feb 2018 21:27:10 GMT

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

Re: Proc SQL convert HEX to character

sunflower88 — Thu, 01 Mar 2018 21:25:25 GMT

It works! Thank you for your help!! 🙂