topic Re: Converting a 19 length number to hex and then to a string in SAS Programming

Converting a 19 length number to hex and then to a string

sascam — Thu, 04 Jun 2020 11:12:41 GMT

Hi everyone,

I need to change a number like this 1105636746057493629 to hex and then back to a string that's looks like a hex e.g. 5902c33b625600 .

This macro works but will only do one at a time, and I need to do thousands of them.

%macro dec2hex(dec);
data _null_;
msg=cat("&dec = #", put(&dec, hex14.));
put msg;
run;

%dec2hex(1105636746057493629);

So I tried this to do them in bulk with this but I get results like 4.7909991E15

data add_new_column;
set work.Full_Report;
connid2 = input(Call_ID, hex14.);
run;

If you're not too busy could you please give some advice?

Re: Converting a 19 length number to hex and then to a string

DavePrinsloo — Thu, 04 Jun 2020 11:13:56 GMT

I assume call_id is a string (char) variable? If so, use put(call_id, $hexNN.).
whe NN is a number = 2x the length of call_id

Re: Converting a 19 length number to hex and then to a string

Kurt_Bremser — Thu, 04 Jun 2020 11:13:02 GMT

Since you cannot store numbers meaningfully beyond 16 decimal digits, I assume that Call_ID is of type character.

The HEX14 informat tells the INPUT function to read 14 characters as a hex value and convert it to an integer.

This means that you get a completely different number, and lose information on the way:

data have;
input longnum $19.;
datalines;
1105636746057493629
11056367460574aaaaa
;

data want;
set have;
longnum_read_as_hex = input(longnum,hex14.);
format longnum_read_as_hex 16.;
run;

The result will be the same for both "numbers".

What do you want to achieve?

Re: Converting a 19 length number to hex and then to a string

sascam — Thu, 04 Jun 2020 12:06:12 GMT

Thankyou for taking the time to reply 😃

What am I trying to achieve?

I have to two tables. One has a primary key as decimal and the other as hexdecimal. E.g.
1105918221034346044 and 5902C33B625600. I need to join them on the PK.

How could I convert the decimal so I can join it with the hexadecimal?

Exponentially yours,

Cameron

Re: Converting a 19 length number to hex and then to a string

Kurt_Bremser — Thu, 04 Jun 2020 12:33:59 GMT

You cannot convert the decimal, as you cannot handle numbers with so many digits in SAS (reliably). You will lose precision from the 16th digit down.

Converting 14 hex digits to a number will also pose problems, as 14 hex digits represent 56 bits, but the 8-byte floating point format uses only 52 bits for the mantissa. Right now I see no way how you could handle this in SAS.

14 hex digits will result in a maximum number of 7.2058E16, which is considerably smaller than your 19-digit number, so I see no way how you could ever correlate those two.

Re: Converting a 19 length number to hex and then to a string

Tom — Thu, 04 Jun 2020 12:51:58 GMT

SAS datasets only uses two data types. Floating point numbers and fixed length character strings. You will need to work with those fields in the source system where those data types are supported. What is the source for this data?

Re: Converting a 19 length number to hex and then to a string

sascam — Thu, 04 Jun 2020 12:58:51 GMT

The source is a DB2 database. I can use Teradata as a middleman

Re: Converting a 19 length number to hex and then to a string

Kurt_Bremser — Thu, 04 Jun 2020 13:05:02 GMT

I am quite sure that, within a single DBMS, the DBA will make sure that the keys have the same attributes (UUIDs nowadays). From where do you get the non-standard keys?

Re: Converting a 19 length number to hex and then to a string

sascam — Thu, 04 Jun 2020 13:18:35 GMT

They aren’t from the same DBMS 😞
We get one in flat file that is imported to SAS. The other is in DB2 as hexadecimal.

Re: Converting a 19 length number to hex and then to a string

Tom — Thu, 04 Jun 2020 13:38:41 GMT

@sascam wrote:
They aren’t from the same DBMS 😞
We get one in flat file that is imported to SAS. The other is in DB2 as hexadecimal.

First step is to make sure not to "import" the flat file. Instead write your own data step to "read" the file. Then you can read that long digit string as a character variable. Then upload that character variable into DB2 and use DB2 code to convert it to the hexadecimal format of the field you need to match.

Re: Converting a 19 length number to hex and then to a string

Kurt_Bremser — Thu, 04 Jun 2020 14:13:48 GMT

I still hold it that you won't be able to make a connection. A 14-digit hex code cannot contain a 19-digit decimal number. It is mathematically impossible.

Re: Converting a 19 length number to hex and then to a string

FreelanceReinh — Fri, 05 Jun 2020 08:19:24 GMT

Hi @sascam and welcome to the SAS Support Communities!

Sorry for being late to the party. It looks like you have a (partially) mathematical problem.

As others have pointed out already, numeric variables in a DATA step are not suitable for 19-digit decimal integers. Luckily, character variables (of length 19) are. With a bit of math you can convert a non-negative 19-digit decimal integer (string) into a 16-digit hexadecimal integer (string). Here's my ad-hoc solution for this (using your example numbers -- see observations no. 1 and 5 -- and a few others):

data have;
input dec $19.;
cards;
1105636746057493629
1105636746057493632
1105636746057490432
0024772835488574589
1105918221034346044
1234567890123456789
9999999999999999999
0000000000000000255
0000000000000000013
0000000000000000000
;

data want(drop=_h);
set have;
length hex $16 _h $10;
_h=put(input(substr(dec,8),12.),hex10.);
hex=put(input(substr(dec,1,7),7.)*5**12+input(substr(_h,1,7),hex7.),hex13.)||substr(_h,8);
run;

Result:

Obs            dec                  hex

  1    1105636746057493629    0F5802C33B602C7D
  2    1105636746057493632    0F5802C33B602C80
  3    1105636746057490432    0F5802C33B602000
  4    0024772835488574589    005802C33B602C7D
  5    1105918221034346044    0F5902C33B62563C
  6    1234567890123456789    112210F47DE98115
  7    9999999999999999999    8AC7230489E7FFFF
  8    0000000000000000255    00000000000000FF
  9    0000000000000000013    000000000000000D
 10    0000000000000000000    0000000000000000

Your macro dec2hex can't do the trick, but it's interesting to see what happens: The hazardous attempt to feed the 19-digit integer 1105636746057493629 into the PUT function forces SAS to deal with an integer whose exact internal representation would require more than the available 52 mantissa bits. As a result, SAS rounds the number in the binary system (at least this happens in this example) so that the surplus least significant bits are all zero and hence can be omitted in the internal representation. I've inserted the corresponding decimal number as the new observation no. 2. As you can see, it equals the original number plus 3 (but the difference could be larger in general). But this is, of course, still too large for the HEX14. format (as @Kurt_Bremser has mentioned: the largest 14-digit hex integer has only 17, not 19 decimal digits: 72,057,594,037,927,935). In such cases SAS takes the last 14 hex digits, in your example 5802C33B602C80. Since the leading hex digit F was cut off, the corresponding decimal number is much smaller (cf. observation no. 4 in my example datasets after adding 3). So, the result of your macro is mathematically incorrect in both the most significant and the least significant hex digits.

The INPUT function in your DATA step tries to interpret the decimal number as a hexadecimal number (as @Kurt_Bremser has pointed out already), which doesn't make sense.

If, for some strange reason, you really don't need the two most significant hex digits, please feel free to use substr(hex,3) instead of hex from my code at your own risk.

My code dissects the original 19-digit integer into the first 7 and the last 12 decimal digits. The latter form an integer which SAS can safely convert into the hexadecimal system. This is done by the PUT function whose result is assigned to character variable _h. Note that 10**12-1, the largest possible 12-digit decimal integer, equals E8D4A50FFF in the hexadecimal system, so length 10 for _h is sufficient. These 10 hex digits are then dissected as well: The last three, substr(_h,8), are the last three hex digits of the final result (variable hex). The remaining first seven hex digits of _h are used in a calculation resulting in the decimal number corresponding to the first 13 hex digits of the final result. (Note that 10**12/16**3=5**12.) Since 16**13-1=2**52-1<2**53, there is no risk of losing precision in this calculation. Finally, this intermediate result is converted into a 13-digit hex number and concatenated with the remaining 3 hex digits mentioned earlier.

Edit:

The underlying mathematical formula is (in SAS notation):

n = (k * 5**12 + m) * 16**3 + s

where

n is the integer to be converted
r=mod(n,10**12) (only quantities derived from r are used in the formula)
k=(n-r)/10**12
s=mod(r,16**3)
m=(r-s)/16**3

In your first example we have:

n=1105636746057493629 (hex: 0F5802C33B602C7D)
r=746057493629
k=1105636
s=3197 (hex: C7D)
m=182142942
k*5**12+m=269930846205442 (hex: F5802C33B602)

Re: Converting a 19 length number to hex and then to a string

sascam — Fri, 05 Jun 2020 00:18:39 GMT

Thank you for taking the time to fix my problem @FreelanceReinh, its working perfectly. That was genius.

Re: Converting a 19 length number to hex and then to a string

ChrisNZ — Sat, 06 Jun 2020 07:44:59 GMT

@FreelanceReinh Inspired to look around by your clever reply, I found that someone as smart as you has written a C# script to convert extremely long positive integers from any base to any other base (bases between 2 and 36).

I adapted it as a macro and it is attached below. There are probably some wrinkles remaining, but it seems to work when I tested a few numbers against the wolfram alpha outputs.

Converting from base 16 to base 10 like this:

%put %ConvertBase( 16, 10, 14C199723CC01C9061D2D278B5596A5EF2B9AB151539EE90FBAB0C4C8485 ) ;

yields

143254356417698708708798794564564587087087987945645645664644646545687685

I'll probably never use it, but here it is. 🙂

Re: Converting a 19 length number to hex and then to a string

FreelanceReinh — Sat, 06 Jun 2020 11:26:31 GMT

Hi @ChrisNZ,

Wow, this is amazing! I'm really impressed. Just yesterday, I wrote down a note (in German) saying "Idea: Write a SAS program or macro or function (FCMP) ..." for exactly this type of conversions (bases 2 through 36 or 37 [underscore]) "... ideally including non-integer numbers ... Example: c='98765', baseconv(c,10,16,6)='0181CD'." I wasn't sure when I would find the time to implement this idea and also thought that someone else would have done this before. And now, only hours later, I'm being presented with a magically working macro! This is simply awe-inspiring.

Thanks a lot for sharing this cool code!

Edit: @Other readers of this thread: Please give likes to ChrisNZ's post. I would give more than one if I could.

Re: Converting a 19 length number to hex and then to a string

ChrisNZ — Sat, 06 Jun 2020 11:48:12 GMT

Ha. Thank you for the compliment. Glad you like it. Not my algorithm though! But pretty nifty: There is no theoretical length limit except 32k. You can still work on a solution for non integers if you want, though I have trouble imagining what A.A hex is in base 10. 10+10/11?
Do share if you write it! 🙂

Re: Converting a 19 length number to hex and then to a string

FreelanceReinh — Sat, 06 Jun 2020 12:01:34 GMT

@ChrisNZ wrote:
There is no theoretical length limit except 32k.

Yes, this is particularly impressive about it.

You can still work on a solution for non integers if you want, ...

Maybe I can focus on numbers between 0 and 1. Including the integer part for arbitrary positive numbers would just mean to prepend the result from your macro.

@ChrisNZ wrote:
... I have trouble imagining what A.A hex is in base 10. 10+10/11?

10+10/16=10.625.

Re: Converting a 19 length number to hex and then to a string

ChrisNZ — Sat, 06 Jun 2020 12:12:25 GMT

> 10+10/16=10.625

Ah yes of course! 🙂

Re: Converting a 19 length number to hex and then to a string

ChrisNZ — Sun, 07 Jun 2020 00:39:10 GMT

@FreelanceReinh Here is a slightly better version, while awaiting yours. 🙂

Re: Converting a 19 length number to hex and then to a string

FreelanceReinh — Mon, 08 Jun 2020 21:30:39 GMT

Great, @ChrisNZ, thanks again, also for motivating me to write code for the fractional part (attached). I went for a PROC FCMP approach, just to try a different route.

A few test cases:

data test;
input inbase outbase outlen fp :$80.;
length cfp $100;
cfp=baseconvfp(fp,inbase,outbase,outlen);
cards;
 10  2 24 2
 10  8  8 2
 10 16  6 2
 32 16  5 TEST
 10  7 55 142857142857142857142857142857142857142857143
  8 10 22 4743357156277512370127634
 16 10 25 0123456789ABCDEFGHIJKL
111 10 30 1 
111 10 30 11111111111111
 28 36  3 M08A
;

Result:

Obs inbase outbase outlen fp cfp 1 10 2 24 2 001100110011001100110011 2 10 8 8 2 14631463 3 10 16 6 2 333333 4 32 16 5 TEST EBB9D 5 10 7 55 142857142857142857142857142857142857142857143 1000000000000000000000000000000000000000000000000000004 6 8 10 22 4743357156277512370127634 6180339887498948482045 7 16 10 25 0123456789ABCDEFGHIJKL 0044444444444444444444444 8 111 10 30 1 009009009009009009009009009009 9 111 10 30 11111111111111 009090909090909090909090909090 10 28 36 3 M08A SAS

This version doesn't contain parameter checks. For people who know what they're doing it can even be useful to go beyond the "limits": For example, "invalid" digits such as G, H, etc. in a hexadecimal input are reasonably interpreted as 16, 17, etc. (see 7th test case: computes 1/16² + 2/16³ + ... + 21/16²² ≈ 1/15² = 0.0044444...). Similarly, bases >36 are no problem (especially on the input side) as long as the digits are not too large. (Application: Compute 1/n to many decimals by converting "1", i.e. 0.1, from base n to base 10. See 8th test case for n=111.)

As mentioned earlier, converting an arbitrary number >=0 is now as simple as concatenating two strings. Example:

%let hex=7E4.0F91;
%let dec=%ConvertBase(16,10,%scan(&hex,1)).%sysfunc(baseconvfp(%scan(&hex,2),16,10,4));
%put &=dec; /* DEC=2020.0608 */

I'm really glad to have these new tools available. They will be useful for investigating numeric representation issues among other things. Thanks @sascam for sparking this discussion.