topic Re: $ebcdic missing 8 bytes with locale off in SAS Programming

$ebcdic missing 8 bytes with locale off

gsnidow — Tue, 22 Apr 2014 17:35:13 GMT

Greetings all. The short of it is that even with the 'locale' option commented out in my config file, the $ebcdic format still fails to translate 8 characters correctly according to IBM-037 code page. This is a great improvement over the 33 missing translations when local is set to en_us, but I still would like to get them all working. I'm using 9.3 on Windows 7 32 bit. I've tried using trantab to edit both the ansiebcd and ebcdic tables, and saved them to my sashelp.host library, but they don't appear to have anything to do with the $ebcdic format. Based on what I found when googling, it seems the $ebcdic format uses translation tables loaded (and uneditable?) at start up. I'm just wondering if there is a way to fix this issue. My work around is to load up an array with the round-trip values of extended ascii to ebcdic(037) and back, then use that array to compare byte by byte. It works, but seems a bit of overkill. I'm using odbc to pull data from DB2, one field of which is binary data that contains fields of text and also packed decimal. I need to convert the binary to hex, then parse out the packed decimal. I know I could just use the DB2 HEX() function to convert before I import, but that would spoil the fun. Anyhow, the attached program should basically show the problem. If you run it, the result will be a table of the 8 error bytes I need to fix. Any help would be much appreciated. Thank you.

Greg

*Edit* - I found this, 14742 - Generated translation tables for $EBCDICw. and $ASCIIw. informatscontain invalid values, which seems to speak to the issue of locale, but also seems to imply the missing values are attributable to '00'x being in many of the cells. However, the 8 I'm missing must be values other than '00'x that are just wrong.

Added link - Greg

Re: $ebcdic missing 8 bytes with locale off

jakarman — Tue, 22 Apr 2014 17:51:11 GMT

Please explain your issue more in detail.

There are chars in Ebcdic that cannot be converted to Ascii. Tey are mostly faked ( not sign, cent sign) the special control chars (eof,lf,cr) also having issues.

You could use utf-8 as used at Windows/office Html, but the mainframe is not supporting that. That issue-s are part of NLS, see:SAS(R) 9.4 National Language Support (NLS): Reference Guide, Second Edition as both sides codepages are important. By the way the latin1 charset of Windows (java) is not the same as that of Unix based ones.

You could get traumatized.

Re: $ebcdic missing 8 bytes with locale off

gsnidow — Tue, 22 Apr 2014 18:57:32 GMT

Jaap, this is about as simple as I can get it...

data _null_ ;

ascii_hex = put(byte(10),$hex2.) ;

ebcdic_hex = put(put(byte(10),$ebcdic.),$hex2.) ;

put 'ascii_hex for decimal value 10 = ' ascii_hex ;

put 'ebcdic_hex using $ebcdic. = ' ebcdic_hex ', but it should be 25' ;

run ; quit ;

There are 8 codepoints not properly translating when using the $ebcdic format with locale= turned off. This is just a visual of decimal value 10 (ascii code point '0A'). It should map to ebcdic codepoint '25', but the $ebcdic format maps it to '15'. The other decimal values not properly translating, as per IBM_037 are 91, 93, 94, 133, 168, 172, and 221)

Specifically, does anyone know of a way to change the mapping of the 8 decimal values listed above when using the $ebcdic format? I've checked my array workaround on millions of observations, and the translations I took from the 'Code page translation' section at the bottom of EBCDIC 037 - Wikipedia, the free encyclopedia are accurate for my data 100% of the time. Thank you.

Greg

Re: $ebcdic missing 8 bytes with locale off

Reeza — Tue, 22 Apr 2014 19:32:14 GMT

Sounds like an issue to bring up with Tech Support.

Re: $ebcdic missing 8 bytes with locale off

jakarman — Tue, 22 Apr 2014 20:58:57 GMT

Greg, your refered to a note 14742 that advices to remove the locale setting .... that is another issue.

I give you the link to MAPEBCDIC2ASCII=TRANTAB catalog-entry (System Option) as it the one defining your own trantab. The associated note there:

Do not change a translation table unless you are familiar with its purpose.

Translation tables are used internally by SAS to implement NLS. If you are unfamiliar with translation tables, do not change the specifications without proper technical advice.

For your fun I18N http://support.sas.com/resources/papers/proceedings13/025-2013.pdf

The systemprogrammer(s) on the mainframe is most likely to have set codepage 034 not 037 (z/OS and DB/2) . The same would be valid for your pc. US more likely 437, Europe as 850.

Using your ODBC-connection I only can see as something connecting to DB/2 it is not a filetransfer. Did you something special to have that in an binary connection? Normally that connection is included with that translation. This will spoil all your attempt on converting binary data to something usefull as you need to revert that ODBC translation (hidden in the driver).

As mentioned it is very classic issue I have seen. The first reference System/370 gx20-1850-3 (1976). Just looking at ISO/IEC 8859-1 - Wikipedia, the free encyclopedia (ascii) and notice the not being defined area's. That is different in the JAVA/Windows version.

Seeing you reference to IBM-037 codepoints 4A and AC you see the mentioned chars refered to extended ascii points. The not sign is not part of normal ascii. That is why you language (including SAS) having more options to that. If your bypass on reversing the ODBC Ebcdic/Ascii is working for you fine. I would not trust that as generic approach.

Re: $ebcdic missing 8 bytes with locale off

Tom — Tue, 22 Apr 2014 22:11:09 GMT

The mapping from EBCDIC -> ASCII and back again is not well defined. Most mappings I have seen are not reversible.

For example how do we map the EBCDIC code 15 (which is intended as NL or NEW LINE) to ASCII? There is not a direct equivalent. Perhaps we should map it to the code for LF. That makes good sense on a Unix system that uses LF as the end of line character in text files.

What are you actually trying to achieve? Perhaps you would get better results using TRANSLATE function instead of the $EBCDIC format?

Re: $ebcdic missing 8 bytes with locale off

gsnidow — Wed, 23 Apr 2014 02:32:33 GMT

Thank you Jaap for the link, some good stuff there. I did use Trantab to update my syshelp.host translation tables, and when it did not solve my problem I googled some more and found, as you have indicated, they are used in file transfer, which I am not doing, so no help there. I think I will just stick with my solution with the array, as it produces the correct result 100% of the time.

Re: $ebcdic missing 8 bytes with locale off

Kurt_Bremser — Wed, 23 Apr 2014 05:38:57 GMT

It has been my experience that the S370/ebcdic formats behave differently over different SAS versions. One of our threads we opened with the SAS hotline even became a genuine error point in SAS 8.2 that lead to a fix.

I solved the problem by applying a macro to all strings read from the host that uses the translate() function to correct the Umlauts and various other characters. When we migrated to 9.2 (on AIX), I had to insert a check for the system version, because in 9.2 the $ebcdic format already gave us the correct characters.

Re: $ebcdic missing 8 bytes with locale off

gsnidow — Wed, 23 Apr 2014 14:00:47 GMT

Tom, what I am doing is this: pull out the packed decimal portions of a binary field, the structure of which, when converted to hex, is an array of elements each 14 characters long. So, take this ebcdic hex string as an example...

C200000006377CD400000054318C

When I import it through ODBC as binary, it looks like gobbledygook, but when converted to ebcdic hex it looks as above. Knowing the structure of the data, I can take the packed decimal (6.2) portions and sum them to get the customers' account balance. I have to lop off the first byte of each element first, the 'C2' and 'D4'. So, in this case, it would be 63.77 (from the first element,C200000006377C) plus 543.18 (from the second element, D400000054318C) to get an account balance of 606.95. The problem is that I have to first convert the binary to hex, which, since my machine is Windows, ends up being ascii hex. So, the above ebcdic hex in ascii ends up being...

420000008604404D000000E891F0

I then have to take this ascii hex string and convert it to ebcdic hex. That is where the missing values trip me up. The easiest solution is to just use the DB2 HEX() function which would preclude me from having to do the conversion on my machine. And this would make the most sense, since I would be letting the mainframe do what it is supposed to do. My array solution also works like a charm. I guess my curiosity in wanting to do it all on my machine is purely academic, just to see if it can be done. Anyhow, I have a couple of solutions so I'll just stick with what works, and accept that there are limitations with round trip conversions between ebcdic and extended ascii. Thank you all for taking a look.

Greg

Re: $ebcdic missing 8 bytes with locale off

Tom — Wed, 23 Apr 2014 16:09:55 GMT

I still do not understand. If you moved it as binary then just use an INFORMAT to read the binary string. Why are you worrying about converting binary fields?

data xx ;

input str $hex12. ;

x = input(str,s370fpd6.2);

put str $hex12. +1 x ;

cards ;

00000006377D

00000054318C

00000060695C

run;

00000006377D -63.77

00000054318C 543.18

00000060695C 606.95

Re: $ebcdic missing 8 bytes with locale off

jakarman — Wed, 23 Apr 2014 16:42:30 GMT

Tom, I believe It was first converted Ebcdic-Ascii by the ODBC interface and of that conversion (packed fields) needed to be translated back.

Binary is binary and at that level there not difference HEX-ascii or HEX-ebcdic. That story is giving me the impression of that additional conversion.

Re: $ebcdic missing 8 bytes with locale off

gsnidow — Wed, 23 Apr 2014 17:07:26 GMT

Tom, the field is an array of structured data. So, the string 'C200000006377CD400000054318C' is two elements, each 7 bytes, with the first byte of each element representing a text account category.

C2 00000006377C D4 00000054318C

The 'C2' represents a category of 'B' (ebcdic 'C2' = ascii '42' = 'B'), with the rest of that element being a packed decimal = 63.77. The next element, starting at the 8th byte, is for category 'M' (ebcdic 'D4' = ascii '4D' = 'M'), with a packed decimal value of 543.18. So, I need to lop off the first byte of each element in the field before I can convert the rest to a decimal value so I can sum them. The DB2 field is 256 bytes, so it is possible for any one observation to have a string of 30 or more 7 byte elements that all need to be have the first byte lopped off then bytes 2 through 7 summed. Is there a way to get rid of the first byte of each element without converting it to a hex string? If so, that might be worth a try. Thanks.

Re: $ebcdic missing 8 bytes with locale off

Tom — Wed, 23 Apr 2014 17:19:40 GMT

If it is moving your data as binary then you should not have any issue with any binary fields in your data. For the text fields that odds of a value being in that field that is not translated "properly" is much smaller and usually not that serious an issue. In the old days we had issues with {} and [] and some other punctuation characters. But since we didn't put non-printing characters into text fields there was normally no major impact from having the values go through an EBCDIC to ASCII tanslation.

If your binary data is being "translated" then as long as the conversion is not mapping more than one value to the same value. (ie mapping both 15x and 25x to LineFeed) then you should be abel to roll you own conversion tool. Put a test record into the database that is 256 characters long and has the values from 00x to FFx. Pull it through the conversion and store it in a file. Then use the translate function to convert the strings back to their original values.

Try this example. Compare the value you get with the dataset below with the values that you receive from pulling the data out of the database to see where your process is different than the what $EBCDIC format assumed would have happened.

data canary ;

converted=put(collate(0,256),$ebcdic256.);

put converted $hex80.;

run;

data want ;

if _n_=1 then set canary ;

input string $20.;

new = translate(string,converted,collate(0,256));

put string $20. / string $hex40. / new $hex40. ;

cards;

1234567890ABCDEF

run;

1234567890ABCDEF

3132333435363738393041424344454620202020

F1F2F3F4F5F6F7F8F9F0C1C2C3C4C5C640404040

Re: $ebcdic missing 8 bytes with locale off

Tom — Wed, 23 Apr 2014 18:14:42 GMT

So your example has fields of length 7 bytes. With the first byte being a character and the next 6 being packed decimal.

You can loop over you larger field and parse it into individual records.

Here is one example way using your sample value.

data xx ;

length field $256 ;

field = 'C200000006377CD400000054318C'x ;

field = translate(field,'40'x,' ');

sum=0;

length next $7 category $1 value 8 sum 8 ;

do i=0 by 1 until (category=' ');

next = substr(field,1+i*7);

category = input(next,$ebcdic1.);

if category ne ' ' then do;

value = input(substr(next,2),s370fpd6.2);

sum=sum(sum,value);

output;

end;

put i= next=$hex14. +1 category= +1 category $hex2. +1 value= 7.2 ;

end;

put sum=;

run;

i=0 next=C200000006377C category=B 42 value=63.77

i=1 next=D400000054318C category=M 4D value=543.18

i=2 next=40404040404040 category= 20 value=543.18

sum=606.95

Re: $ebcdic missing 8 bytes with locale off

jakarman — Wed, 23 Apr 2014 18:56:37 GMT

Let us keep it simple. When at the DB2 the records with their fields would have defined as separate fields being character and numeric ones, it was the ODBC / DB2 connection doing the translation.

As the problem is being introduced by referencing them as a combined field being first translated ebcdic-ascii it needed to be translated back into the original bytes.

Re: $ebcdic missing 8 bytes with locale off

gsnidow — Wed, 23 Apr 2014 19:12:34 GMT

Thank you Tom. That is kind of what I am doing with my array solution posted at the start of the thread (although not nearly as elegantly as you have done). That brings me back to my issue with ascii to ebcdic translations. Take, for example, this one, which I have translated using my own array of ibm-037.

C200000008158CD500000003216C (this is what I need the resulting hex string to be)

Since I am getting the data as binary, I have to first convert it to hex (ascii), which gives me...

420000009785F04E000000038125, which then has to be converted to ebcdic hex, and therein lies my problem

Notice this(added spaces intentional) conversion when I use $ebcdic to convert the ascii hex to ebcdic..

4200000097 85 F04E000000038125 -->> C200000008 25 8CD500000003216C

Everything is fine except for the conversion of the '85'x to '25'x. According to ibm-037, it should be converting to '15'x, so any fields that have a '85'x in them as ascii hex give the wrong answer.

I did just speak to one of the mainframe guys who told me this field was designed to be fed into a COBOL function on the client 3270, converted to hex, then sliced and diced by the COBOL. It was not intended for a direct manipulation by any other program. Given that tidbit, however, I still think $ebcdic could work if I could just fix the mappings of the 8 errored characters.

Greg

Re: $ebcdic missing 8 bytes with locale off

gsnidow — Wed, 23 Apr 2014 19:26:27 GMT

Jaap, this is coming from one column in a DB2 table, not separate columns combined upon import. I don't think anything is wrong with the odbc import, since I can, with 100% accuracy, turn the binary fields into the ebcdic hex they need to be, its just that I can't use a sas format to do it. I have compared my ebcdic conversions to what I can get out of TSO on literally tens of millions of row without any errors. I've been trying to get this to work for around 2 years now, and the only issue I can detect is the use of $ebcdic. Anyhow, I sure appreciate all the help everyone has given me here.

Greg

Re: $ebcdic missing 8 bytes with locale off

Tom — Wed, 23 Apr 2014 20:31:37 GMT

I am still confused about where the problem is being introduced. Let me restate and you can tell me what I have done wrong.

First let's use SAS notation and use hex literals ("hhhh"x) to reference values that have been converted to hex so humans can read them and normal quoted strings ("fred") for values that are the actual characters. (for example the three character string "123" would be stored in EBCDIC as the three byte string "F1F2F3"X and in ASCII as the string "313233"X )

Say your database variable is named FIELD.

My understanding is that the data in the database is stored as BINARY. So for example the first 14 bytes of the 256 byte field is 'C200000008158CD500000003216C'X.

If this field is moved by your database connection as BINARY data then it will look exactly the same in SAS. So if in SAS you used the $HEX format to look at the value (put field $hex28.;), then you would see in the log the 28 character string 'C200000008158CD500000003216C' .

If that is the case then the code above should work without any issues. There is no need to do anything within SAS to try and convert this binary field to or from ASCII. (Other than if you want to interpret one or more of the bytes as EBCDIC characters as in the code above.)

So let's assume that it is NOT the case. This means that your database connection does NOT allow you to move a field as binary and has instead imposed some type of transcoding on the data.

Now if SAS had done the conversion using the $ECBDIC informat (instead of your database connection software) then you would see in your log from the previous PUT statement a string like:

'420000000000F04E000000038125'. Your string might be different as the conversion has been either done somewhere before SAS gets a hold of it or using some other component of SAS. In fact it better NOT match the string generated by the $EBCDIC. format because that format maps 28 different bytes to '00'x, as so it is NOT reversible. But as long as the translation that your database connect does use maps each possible byte is to a unique byte then you can reverse the translation by creating a translation table or string or whatever you want.

So if you are in this situation then you want to apply something like the TRANSLATE function to FIELD to convert it back to the BINARY data that it used to have ( that is back to 'C200000008158CD500000003216C'X ) and then you can use the previous post to parse it into blocks and convert the text part to ASCII and the number part to numbers. A good way to test that the translation is reversible is to fill a record in the database will all 256 possible byte values and pull it into SAS. This will also have the added benefit of generating the translation string that you will need to use to reverse the conversion.

Now if you cannot get that to work because the transcoding process is NOT reversible then you will need to find a way to convert the binary field to hex strings and copy that from the database into SAS. For example in SAS I would use the PUT() function with the $HEX. format to convert a string into its hex equivalent. I do not know what statement you would use on DB2 to do that. So now once you have your now 512 character string into a SAS variable you can use the $HEX informat to convert it back to its original binary value. So using the same 14 byte sequence this means that in DB2 you would convert the 14 byte string 'C200000008158CD500000003216C'x to the 28 byte string "C200000008158CD500000003216C". Let's call this new variable HEXFIELD. This would then be copied into SAS without any issue as it will only be using 16 characters. Then in SAS you could re-create FIELD by using ( field = input(hexfield,$hex28.) . There is no need to reference any translation functions or arrays as you now have your actual binary strings back and can process them. You might have some issue if your values are variable length. But you can probably easily deal with that by either padding properly or also copying over a variable with the length.

Re: $ebcdic missing 8 bytes with locale off

gsnidow — Wed, 23 Apr 2014 22:28:57 GMT

Tom, thank you so much for putting so much effort into understanding the issue. I'm not sure if there is any transcoding going on in the odbc import, but you are exactly correct in your paragraph starting with 'So let's assume that it is NOT the case'. After importing, when I put the field into $hex format, I get the ascii string '420000000000F04E000000038125', and, even though I get errors when I try to put it to ebcdic hex, it is translated from the binary to ascii hex 100% correctly 100% of the time. It seems odd that it cannot make the round trip conversion, but so it is. Using your example field name of 'FIELD' in the DB2 table, the DB2 equivalent of $hex is the SQL function HEX(). In my odbc sql, I can do 'SELECT HEX(FIELD) FROM TABLE', and I get the correct ebcdic hex string 100% of the time. I am going with your suggestion that the transcoding process may not be reversible, and I will have to live with my array solution, or use the HEX() function in my sql. You've given me lots to think about, and if I make any progress, I'll post back to this thread. Thank you again.

Greg

Re: $ebcdic missing 8 bytes with locale off

Tom — Wed, 23 Apr 2014 22:44:34 GMT

I do not think there is any need for you to worry about translation if you can get DB2 to convert to HEX and then convert back from HEX to binary.

So if you perform this query.

proc sql ;

connect to odbc (.....);

create table sasdata as

select input(hexfield,$hex512.) as field

from connection to odbc

(select hex(field) as hexfield from .... )

;

quit;

Then you should have the data back as it was originally .

Then you could process the first 7 bytes using:

data want ;

set sasdata;

category = input(substr(field,1,1),$ebcdic1.);

value = input(substr(field,2,6),s370fpd6.2);

run;