An issue, with hex-variable

[clauspollas]

I read some data from a Netezza database, with this nice data, e.g.:

F2F0F2F060F0F860F0F360F0F34BF1F24BF2F94BF9F4F6F7F4F1F3F8F3F7F8F9404040

It is a timestamp (YYYY-MM-DD-HH.MM.SS.999999999999). But SAS can´t read hex-vars longer than 16 chars, as numbers. If I input the above, with $hex70. it returns nonsense.

Netezza keeps timestamps like this:

Min: -63,082,281,600,000,000 (00:00:00, 1/1/0001)

Max: 252,455,615,999,999,999 (23:59:59.999999, 12/31/9999)

Any suggestions?

[ballardw]

It may help someone that knows more about Netezza than I do (nothing), to show exactly how you are connecting to the database and how you read data.

My understanding is that some of the methods involved do "in the background" variable conversion for some of the data types. So the detail may matter.

[Kurt_Bremser]

Seems like Netezza counts microseconds, and uses a day late in 2000 as day zero:

 73         data _null_;
 74         x1 = 252455615999.999; /* value shortened by 3 digits, as we are reaching the limits of SAS numerical precision */
 75         x2 = datepart(x1);
 76         x3 = "31dec9999"d - x2;
 77         format x1 e8601dt26.6 x2 x3 yymmdd10.;
 78         put _all_;
 79         run;
 
 x1=9960-01-02T23:59:59.998993 x2=9960-01-02 x3=1999-12-30 _ERROR_=0 _N_=1

There may be a slight shift, depending on which future years are considered as leapyears. You would be better off comparing the current date between SAS and Netezza.

70 hex characters are suspicious; a 32-byte longint would result in 64 hex characters.

[Tom]

Looks like an EBCDIC string to me.

523   data test;
524     hexstring='F2F0F2F060F0F860F0F360F0F34BF1F24BF2F94BF9F4F6F7F4F1F3F8F3F7F8F9404040';
525     string  = input(input(hexstring,$hex70.),$EBCDIC35.);
526     put (_all_) (=/);
527   run;


hexstring=F2F0F2F060F0F860F0F360F0F34BF1F24BF2F94BF9F4F6F7F4F1F3F8F3F7F8F9404040
string=2020-08-03-03.12.29.946741383789
NOTE: The data set WORK.TEST has 1 observations and 2 variables.
NOTE: DATA statement used (Total process time):

So once you have it as ASCII codes instead it should be easy to convert '2020-08-03-03.12.29.946741383789' into a number.  Note that you will probably not be able to store it will all of those decimal places on fractions of a second.

542   data test;
543     hexstring='F2F0F2F060F0F860F0F360F0F34BF1F24BF2F94BF9F4F6F7F4F1F3F8F3F7F8F9404040';
544     string  = input(input(hexstring,$hex70.),$EBCDIC35.);
545     date = input(string,yymmdd10.);
546     time = input(substr(string,12),time20.);
547     dt = dhms(date,0,0,time);
548     format date yymmdd10. time time15.5 ;
549     format dt datetime30.5;
550     put (_all_) (=/);
551   run;


hexstring=F2F0F2F060F0F860F0F360F0F34BF1F24BF2F94BF9F4F6F7F4F1F3F8F3F7F8F9404040
string=2020-08-03-03.12.29.946741383789
date=2020-08-03
time=3:12:29.94674
dt=03AUG2020:03:12:29.94674

[clauspollas]

pardon, my fault. It is NOT hex, it is masked data. Apologies.

[Kurt_Bremser]

Ha, that three 40s should have given me the EBCDIC clue. But it's been quite some time since I pulled my data from a real mainframe.