topic Reading EBCDIC Files with null characters ('00'x) in fields in SAS Programming

Reading EBCDIC Files with null characters ('00'x) in fields

PAzevedo — Wed, 04 Apr 2012 11:43:15 GMT

Hi there,

I'm trying to read some files in EBCDIC from a Mainframe, to our HP-UX SAS Servers.

I'm accessing the files directly in the Mainframe using filename ftp.

The resulting datasets look fine but i'm facing a problem on some fields where there are null ('00'x) characters in the midle of some fields, as the info in those fields gets truncated as the first null charecter is found.

Next is a hexadecimal representation of one of those fields, with a length of 30, taken from the _infile_ variable. I've underlined the severel ocurrences of the null characters.

F2F0F2F5F6F0F0F0F0F0F1F4F1C200000000000CF2F0F1F2F0F3F2F00000

When i translate these fields from EBCDIC to ASCII, using the $ebcdic30. informat in the input statement, all the info after the first null gets truncated leaving me with an incomplete record:

F2F0F2F5F6F0F0F0F0F0F1F4F1C2

From what i could already analyse, the information read from the input buffer is correct, i've analysed the source record in TSO and the hex representation matches the one i'm reading in SAS.

The problem seems to be when converting with the ebcdic informat.

Any idea on how to overcome this issue?

Thks in advance.

Regards.

I'll attach the code and some partial log for any debuging needs (The table "estruturas" has the structure of the information to be read in the LOG file):

Reading EBCDIC Files with null characters ('00'x) in fields

art297 — Wed, 04 Apr 2012 12:25:06 GMT

Try the filename option: encoding=unicode

Reading EBCDIC Files with null characters ('00'x) in fields

PAzevedo — Wed, 04 Apr 2012 12:48:44 GMT

It gives a Warning message

WARNING: A character that could not be transcoded was encountered.

and no obs are written to the datasets as the value that controls wich dataset to output (log_tabela) is not correctly translated

log_tabela=’’’’’’’’’’’’’’’’’’

Reading EBCDIC Files with null characters ('00'x) in fields

art297 — Wed, 04 Apr 2012 13:23:56 GMT

I would suggest, first, that you delete this discussion, change your password, and start a new discussion. The log you posted has your ip address, user name and password included.

When you repost, yes the code and log would help, but seeing at least a snippet of the data would also be quite helpful.

Reading EBCDIC Files with null characters ('00'x) in fields

PAzevedo — Wed, 04 Apr 2012 13:38:38 GMT

Thks for the warning.

The IP address is from a closed internal network so there's not a problem there. The user and pass might be a problem if you can access the internal network.

I've changed the pwd anyways and edited the log file.

I'm not sure on what data i can show you as i can only access the file trough a TSO terminal. The file is variable length and, as you can see in the code, it's read based on a structure file wich indicates what to read in each record.

Reading EBCDIC Files with null characters ('00'x) in fields

art297 — Wed, 04 Apr 2012 13:51:27 GMT

It has been too many years since I've worked with Mainframe systems, thus I can only guess. However, looking at the snippet of data that I could see in your log, it definitely doesn't appear to be unicode.

The following, from the documentation, might help:

$CHARZBw.: reads character data and converts any byte that contains a binary zero to a blank.
$EBCDICw.: converts character data to EBCDIC. Under z/OS, $EBCDIC and $CHAR are equivalent.

Reading EBCDIC Files with null characters ('00'x) in fields

PAzevedo — Wed, 04 Apr 2012 14:20:52 GMT

I'm getting somewhere with the $CHARZBw. informat.

When inputing the data with that informat the nulls are replaced with '20'x (hex code for space in ASCII). If afterwards i apply the $EBCDICw. informat the data is well translated to ASCII but instead of blanks in place of nulls i get '€' wich is the EBCDIC symbol for the '20'x hex code.

Now i just have to find how the $CHARZBw. informat translates to EBCDIC space hex code and not ASCII space hex code.

At the end, '€' character might be as good garbage as the null characters that were in the field.

Thks for your help.

Any more comments are much appreciated.

Regards.

Re: Reading EBCDIC Files with null characters ('00'x) in fields

Tom — Wed, 04 Apr 2012 15:53:13 GMT

Read it using $CHAR.

Translate the '00'x to '40'x (that is space in EBCDIC).

Convert to ASCII using $EBCDIC informat.

57   data _null_;
58    hex='F0F2F5F6F0F0F0F0F0F1F4F1C200000000000CF2F0F1F2F0F3F2F0000000';
59    raw=input(hex,$hex60.);
60    fix=translate(raw,'40'x,'00'x);
61    ascii=input(fix,$ebcdic30.);
62    put hex=/ (raw fix ascii) (= $hex. /) / ascii=;
63   run;
hex=F0F2F5F6F0F0F0F0F0F1F4F1C200000000000CF2F0F1F2F0F3F2F0000000
raw=F0F2F5F6F0F0F0F0F0F1F4F1C200000000000CF2F0F1F2F0F3F2F0000000
fix=F0F2F5F6F0F0F0F0F0F1F4F1C240404040400CF2F0F1F2F0F3F2F0404040
ascii=3032353630303030303134314220202020200C3230313230333230202020
ascii=025600000141B      
20120320

Note that you string includes '0C'x which is normally a page feed.

Re: Reading EBCDIC Files with null characters ('00'x) in fields

PAzevedo — Wed, 04 Apr 2012 15:56:04 GMT

art or anyone else,

Is there any trick to make SAS consider converting the binary 0s to EBCDIC space hex code ('40'x) instead of ASCII hex code ('20'x) when using informat $CHARZBw?

It turns out that having a € for the binary 0 doesn't suit my problem, i really need it to be converted to spaces.

Regards.

Re: Reading EBCDIC Files with null characters ('00'x) in fields

FriedEgg — Wed, 04 Apr 2012 16:15:23 GMT

I agree with Tom and will reiterate his note, '0C'x is a form feed (page break) so be aware of this in your data and probably be prepared to remove it.

/* resemble your file */
filename tmp temp;
data _null_;
 file tmp;
 hex='F0F2F5F6F0F0F0F0F0F1F4F1C200000000000CF2F0F1F2F0F3F2F0000000'x;
 put hex;
run;
/* read your file */
data foo;
 infile tmp;
 input @;
 _infile_=prxchange('s/\x00/@/',-1,_infile_);
 input want $ebcdic30.;
run;
want=025600000141B      
20120320

Reading EBCDIC Files with null characters ('00'x) in fields

PAzevedo — Wed, 04 Apr 2012 16:24:08 GMT

Hi Tom,

Reading the file with the $CHARw informat has the same behaviour than with $EBCDICw truncating the field after it encounters the null.

Picking in your sugestion, i'm creating one variable containing the whole _infile_ buffer converted to hexdecimal to apply your translation code afterwards. After this parsing i'll have to substring to get every column. This seems a very heavy solution but it is, until now, the best one i have.

Reading EBCDIC Files with null characters ('00'x) in fields

Tom — Wed, 04 Apr 2012 16:28:58 GMT

Just use the _INFILE_ trick as posted above by FriedEgg. The regex is overkill though for such a simple problem.

data want ;

infile .... ;

input @;

_infile_=translate(_infile_,'40'x,'00'x);

input field1 $ebcdic20. field2 $ecbcdic12. .... ;

run;

Re: Reading EBCDIC Files with null characters ('00'x) in fields

FriedEgg — Wed, 04 Apr 2012 16:40:24 GMT

PAzevedo,

See my posting, you can read the columns with a normal input statement after adjusting the buffer as I did in the example, here I parse your data into a series of numeric and character variables for each byte.

data foo;
 infile tmp;
 input @;
 _infile_=prxchange('s/\x00/@/o',-1,_infile_); *or translate, like Tom, I just prefer regex;
 input (v1-v12) (s370ff1.) (v13-v19) ($ebcdic1.) (v20-v30) (s370ff1.);
 put 32*'-' / (v1-v30) (10*3. /) / 32*'-';
run;
--------------------------------
  0  2  5  6  0  0  0  0  0  1
  4  1B                      2
  0  1  2  0  3  2  0  .  .  .
--------------------------------

Reading EBCDIC Files with null characters ('00'x) in fields

PAzevedo — Wed, 04 Apr 2012 16:41:55 GMT

Hi FriedEgg.

That specific character isn't a problem for me as SAS has no problem in storing it, although others like '0A'x, '0D'x, '3F'x, '25'x are.

There is a bunch of garbage in our Mainframe tables that have given us a lot of headaches trough time!

In order to give the almost full picture, we are changing all our SAS applications to start getting the info we need directly from the Mainframe, trough FTP, instead of beeing the mainframe to do the parsing of the files and then sending them to our SAS servers (a new offload policy). This means that every parsing being made nowadays in the applications that send us the files needs to be done exactly the same way (e.g. delimiters, special characters, etc.) by our SAS Apps or else there is a huge risk on the new data not matching the one we have nowadays.

The '0C'x isn't currently being parsed by the Mainframe apps so it will stay the same in the new SAS App.

But, now that you and Tom mentioned it, i'm starting to feel that there isn't really a simple and light solution for my problem as i expected.

I've runned away from pearl expressions for a long time but i believe now it's the right time to dive into it.

Thks for your help.

Regards.

Reading EBCDIC Files with null characters ('00'x) in fields

PAzevedo — Wed, 04 Apr 2012 16:44:59 GMT

I hadn't read FriedEgg's post when i answeared you.

Re: Reading EBCDIC Files with null characters ('00'x) in fields

PAzevedo — Wed, 04 Apr 2012 16:47:32 GMT

I got it, thks.

You'll have to escuse me as it takes a while for me to write the answears so that the english errors are minimum!

And now the big doubt is... who gets the 4 points and who gets just 3.

Re: Reading EBCDIC Files with null characters ('00'x) in fields

FriedEgg — Wed, 04 Apr 2012 16:58:45 GMT

Use \s instead of \x00 to catch all 'white-space' characters and convert them to '40'x (@ in ascii)

filename tmp temp;
data _null_;
 file tmp;
 hex='000A0D3F250C00F2F4F6000A0D3F250C00F2F4F6000A0D3F250C00F2F4F6'x;
 put hex;
run;
data foo;
 infile tmp recfm=f lrecl=31 truncover; *this is 31 instead of 30 because of construction above in unix adds LF;
 input @;
 _infile_=prxchange('s/\s/@/',-1,_infile_);
 input (v1-v30) ($ebcdic1.);
run;

Reading EBCDIC Files with null characters ('00'x) in fields

PAzevedo — Wed, 04 Apr 2012 17:15:04 GMT

I didn't quite get what you mean by 'white-space' characters. I see that it removes all the characters i mentioned before but not the '0C'x.

What is the difference?

Re: Reading EBCDIC Files with null characters ('00'x) in fields

PAzevedo — Wed, 04 Apr 2012 17:22:00 GMT

I got it.It was one google search away!

I don't know if i can remove all 'white-spaces' for the same reason i've got to keep the '0C'x so that the data read before the new SAS App matches the one read after. I'll look into it a i'll for sure start looking a lot more into pearl.

Thks.

Re: Reading EBCDIC Files with null characters ('00'x) in fields

FriedEgg — Wed, 04 Apr 2012 17:30:08 GMT

Whitespace character information:

http://en.wikipedia.org/wiki/White_space_(computer_science)

I am not sure why it seems to be not including some of them in this case.

data foo;
 infile tmp recfm=f lrecl=31 truncover; *this is 31 instead of 30 because of construction above in unix adds LF;
 input @;
 _infile_=prxchange('s/[\x00\x0A\x0D\x3F\x25\x0C]/@/',-1,_infile_);
 list;
 input (v1-v30) ($ebcdic1.);
 put (v1-v30) (10*3. /);
run;
  .  .  .  .  .  .  .  2  4  6
  .  .  .  .  .  .  .  2  4  6
  .  .  .  .  .  .  .  2  4  6
RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0                      
1         @@@@@@@òôö@@@@@@@òôö@@@@@@@òôö@