03-30-2015 10:41 AM
I’m new here but I’m after some assistance.
I have several txt files containing packed fields that I’m trying to extract into SAS
This is fairly straight forward except for the last field which is of varying length
The first part I have is
@1 Customer_Type $ebcdic1.
@2 Customer_Code s370fpd5.
@7 Address_Indicator S370FIB4.
@11 Format_Of_Address $ebcdic2.
@13 Country_Code $ebcdic2.
@15 Registartion_Date $ebcdic10.
@25 Last_updated_by $ebcdic8.
@33 Last_Updating_Cpmpany $ebcdic4.
@37 Last_Updating_Branch $ebcdic4.
@41 Date_Last_Updated $ebcdic10.
@51 Time_Last_Updated $ebcdic8.
So far so good, however the next part is the tricky bit
The next field is a maximum of 280 characters, the first 2 characters are a packed field (S370FIB2.) that contain the length of the rest of the field which is in ebcdic
I can’t get it to recognise both the varying length and the fact that both fields are encoded the best I have so far is
@59 Adress_Length S370FIB2.
+2 Address $varying278. Adress_Length ;
But the final field is still encoded
Is anyone able to help with this?
03-30-2015 12:46 PM
You need both a varying-length input and conversion from EBCDIC, which I don't think can be accomplished with one informat.
I'd suggest you leave the code as it is, and then perhaps
length Final_Address $278;
Final_Address = input(Address, $ebcdic8.);
after your input statement might do the conversion. Unfortunately, I don't have any way to test it, but good luck.
03-31-2015 06:28 AM
Thanks, sadly that does not seem to work
It converts the field to text but it does not recognise the varying length so the next record starts half way through the field
I suspect I need to convert the field with EBCDIC first and then get it to recognise the varying length so it knows to start the next record in the next row
03-31-2015 07:50 AM
How exactly are you reading the file?
Directly on the mainframe, or on another platform after a file transfer, or on another platform with the ftp access method?
03-31-2015 10:04 AM
My preferred option would be to read the file directly from the mainframe using the FTP access method, because that way you can let the MF and SAS take care of the whole record-length problem by using the s370v and rcmd="site rdw" option in the filename statement.
You can also use the s370v option locally if the FTP if the file transfer was done with the "site rdw" sent before the get command. The MF then supplies the record length before each record in a 4 byte word, which the s370v option interprets correctly.
Did your original try succeed in reading the next record correctly?
If yes, then
Adress = substr(input(Adress,$ebcdic278.),1,Address_length);
should work in converting the value.
03-31-2015 08:20 AM
I had a try at what Tom suggested but it didn’t seem to work as desired, the final field wasn’t the variable value. But I’ll have another crack at it in case I missed something
03-31-2015 06:41 AM
Does the file have varying record length? If yes, don't forget the truncover option on the infile statement, as it is necessary when reading without $varying.
Since you have no gaps when reading the input data, I suggest to omit the positions, they're just a PITA to maintain if the record structure changes.
So, at the end of the input statement, do
After that, do
Address = substr(Address,1,Adress_Length);
to clean out any unwanted hex 0's
unless you want to keep it for future reference.
03-31-2015 08:03 AM
Why are you suggesting that @aktr make so many changes to an otherwise working program? Why do you think the record structure will change? Seems useless to me.
03-31-2015 08:20 AM
Been there, done that. Hundreds of times.
And I do NOT propose "so many changes". I just stated that his use of pointers is completely unnecessary and will cause him avoidable grief if the record structure changes (which it will, as experience has shown).
And my method is the one that worked here, when we still read from the mainframe. Nowadays we get our data from the Microfocus pseudo-mainframe environment on Linux, so the ebcdic conversion is a matter of the past, but varchar fields, VB file formats, and packed decimals are still there.
03-31-2015 10:03 AM
Actually the fact I don’t need pointers is very useful to know, it would have saved me some grief in another report
I have tried both Tom and Kurt’s approaches and sadly nether seems to work.
The field still continues to the full 278 characters so includes the start of the next record
03-31-2015 10:42 AM
So your big problem is the varying record length of the mainframe's VB file format, which is not represented by any LF or CR/LF markers.
So you need to work with the s370v option (locally or direct ftp), or try to read the file as a stream (recfm=n) without record boundaries (what I never did, because the s370v method works like a charm).
If you can get direct FTP access, read the file like that:
filename in ftp "'host.filename'" host="hostname" user="username" pass="password"
s370v rcmd="site rdw" lrecl=max_record_length+4;
Take note of the single quotes within the double quotes when supplying the filename!
infile in truncover;
/* all the variables you read */
/* here copy the substr() from my first post in this thread */
The infile will now behave like any other file of varying record length.
03-31-2015 11:52 AM
1. Are the values of Adress_Length correct?
2. If your records are VB format, you'll need to get SAS processing it properly. I don't have any way to test is, but I recall that on S370 VB records have a four-byte length field preceding the record data. You'll need to get SAS to use it correctly.
Don't worry about getting the fields out of the record for now; just figure out how to correctly read each record without overlapping into the next. Then the fields should be fairly easy.