DATA Step, Macro, Functions and more

Proc SQL convert HEX to character

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Proc SQL convert HEX to character

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_IdLocation_Name
 FL
 OH
 IL
 HI
 CO

 

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


Accepted Solutions
Solution
‎03-01-2018 04:24 PM
PROC Star
Posts: 631

Re: Proc SQL convert HEX to character

Posted in reply to sunflower88

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;

Thanks,
Suryakiran

View solution in original post


All Replies
Regular Contributor
Posts: 237

Re: Proc SQL convert HEX to character

Posted in reply to sunflower88
Occasional Contributor
Posts: 6

Re: Proc SQL convert HEX to character

Posted in reply to tomrvincent
Any solution for PROC SQL instead of data step?
Regular Contributor
Posts: 237

Re: Proc SQL convert HEX to character

Posted in reply to sunflower88
put(v1_hex||v2_hex, $hex8.) as
v1v2_char

 

Occasional Contributor
Posts: 6

Re: Proc SQL convert HEX to character

Posted in reply to tomrvincent

Thank you for your help!

 

However,  it  doesn't work for me......  :-(

PROC Star
Posts: 631

Re: Proc SQL convert HEX to character

Posted in reply to sunflower88

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

Thanks,
Suryakiran
Occasional Contributor
Posts: 6

Re: Proc SQL convert HEX to character

Posted in reply to SuryaKiran

Us odbc connect to SQL database

 

Thanks for the help!

Solution
‎03-01-2018 04:24 PM
PROC Star
Posts: 631

Re: Proc SQL convert HEX to character

Posted in reply to sunflower88

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;

Thanks,
Suryakiran
Occasional Contributor
Posts: 6

Re: Proc SQL convert HEX to character

Posted in reply to SuryaKiran

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

 

Any idea?

PROC Star
Posts: 631

Re: Proc SQL convert HEX to character

Posted in reply to sunflower88

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

Thanks,
Suryakiran
Occasional Contributor
Posts: 6

Re: Proc SQL convert HEX to character

Posted in reply to SuryaKiran
It works! Thank you for your help!! :-)
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 316 views
  • 0 likes
  • 3 in conversation