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

[sascam]

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?

[DavePrinsloo]

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

[Kurt_Bremser]

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?

[sascam]

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

[Kurt_Bremser]

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.

[Tom]

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?

[sascam]

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

[Kurt_Bremser]

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?

[sascam]

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.

[Tom]

---

@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.

[Kurt_Bremser]

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.

[FreelanceReinh · Accepted solution]

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)

[sascam]

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

[ChrisNZ]

@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. 🙂

[FreelanceReinh]

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.