BookmarkSubscribeRSS Feed
aktr
Calcite | Level 5

Hi,

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

     LRECL=340

     INPUT

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

Many thanks

Andy

14 REPLIES 14
TomKari
Onyx | Level 15

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.

Tom

aktr
Calcite | Level 5

Hi,

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

Cheers

Andy

Kurt_Bremser
Super User

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?

aktr
Calcite | Level 5

Its FTP’d from mainframe onto a local server by a different department and then I’m reading it from there

Kurt_Bremser
Super User

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.

data_null__
Jade | Level 19

Look closely at what is suggesting.  Read the field with existing code $VARYING then re-read the VARIABLE with INPUT function and $EBCDIC278.

aktr
Calcite | Level 5

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

Thanks

Kurt_Bremser
Super User

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

Adress_Length  S370FIB2.

Address $ebcdic278.

;


After that, do

Address = substr(Address,1,Adress_Length);

to clean out any unwanted hex 0's

and

drop Adress_Length;

unless you want to keep it for future reference.


data_null__
Jade | Level 19

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.

Kurt_Bremser
Super User

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.

aktr
Calcite | Level 5

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

Kurt_Bremser
Super User

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!

data your_dataset;

infile in truncover;

input

/* all the variables you read */

;

/* here copy the substr() from my first post in this thread */

run;

The infile will now behave like any other file of varying record length.

data_null__
Jade | Level 19

You need to show the modified code you are using.  Also seeing the log messages from the data step will be helpful too.

TomKari
Onyx | Level 15

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.

Tom

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 14 replies
  • 1935 views
  • 0 likes
  • 4 in conversation