to_char in Oracle equivalent in PROC SQL

Reply
Occasional Contributor
Posts: 9

to_char in Oracle equivalent in PROC SQL

Hello,

 

We have an encoded ID that I'm getting out of a reporting system and attempting to match to our Oracle database.

 

The function that the Oracle team uses to decode the ID is 

TO_CHAR(REPLACE(:segment_1,'-') + 100000000,'xxxxxxxxxxxxxxxx')

 

TO_CHAR seems to be an Oracle function that conversts a hex number to decimal.  

 

Is there an equivalent of the above in PROC SQL?

 

Thanks!

Super User
Posts: 17,905

Re: to_char in Oracle equivalent in PROC SQL

I don't think that's what it's doing. TO_CHAR, converts a number to a character field. PUT() is the equivalent, but your function also uses Replace and some other transformations so it's not a direct mapping. Most likely TRANSLATE will also be required. It may be easier to post what your before and after look like, or what your transformation rules are.
Occasional Learner
Posts: 1

Re: to_char in Oracle equivalent in PROC SQL

Hi Reeza,

I don't know the answer, but I can say what the Oracle code is doing.

It amounts to:

dec to hex ( (result of REPLACE Expression)+100000000)

Or maybe if you grok perl

perl -e 'print sprintf("%x", (EXPRESSION RESULT +100000000)); '

I think the slightly tricky part is that to convert from hex to dec with Oracle, use TO_NUMBER, but from dec to hex you use TO_CHAR..

So how would this work with SAS?

Hope that helps.





Super User
Super User
Posts: 6,502

Re: to_char in Oracle equivalent in PROC SQL


efrazier wrote:
Hi Reeza,

I don't know the answer, but I can say what the Oracle code is doing.

It amounts to:

dec to hex ( (result of REPLACE Expression)+100000000)

Or maybe if you grok perl

perl -e 'print sprintf("%x", (EXPRESSION RESULT +100000000)); '

I think the slightly tricky part is that to convert from hex to dec with Oracle, use TO_NUMBER, but from dec to hex you use TO_CHAR..

So how would this work with SAS?

Hope that helps.






DEC to HEX makes not much sense to me. Can you translate what you mean by DEC? And HEX?

If you have a number and you want to convert to hexadecimal use the PUT() function with the HEX. format.

  charvar = put(numvar,hex16.);

  charvar = put(4+5, hex16.);

 

Ask a Question
Discussion stats
  • 3 replies
  • 948 views
  • 0 likes
  • 4 in conversation