Data creation from raw text

[enigma84]

Hi Folks,

Have attached 2 files, the txt file is a flat file which contains data in multiple rows. The good thing is that it is fixed width kind of a data. Have used input to gather information, but getting it beyond one row is turning out to be difficult.

The way in which i need is in the attached excel. Will greatly appreciate any help.

Regards,

Ketan

[Patrick]

You are trying to read data created by a report. It would be much easier/better to try and get access to the data used to generate this report. It is possible to read such data but it can take up quite a bit of time to get it right and cover every single layout variation.

Below code illustrates how it could be done. It also shows for the second last transaction (ACCOUNT VERIFY) how the report layout can vary creating additional cases to be dealt with - so additional checks and input statements.

In short: It could become quite labor intensive to get this 100% right for your real data and it will only work for the report "at hand". If you then want to re-run your code for a later report there might be another "layout" in it for which you haven't coded yet.

data sample(drop=_exp);

  attrib

    account_nr length=$19

    marker length=$2

    transaction_dt length=8 format=date9.

    text length=$200

    ;

  retain account_nr;

  infile 'c:\test\sampledata.txt' truncover dlm=' ';

  input @23 transaction_dt ?? :ddmmyy10. @;

  if not missing(transaction_dt) then

    do;

      if anydigit(_infile_)=2 then input account_nr $ 2-20 @;

      input @22 marker @32 x1 :best32. x2 :comma32.;

      input @;

      _exp=find(_infile_,'*EXP DATE INVALID OR EXPIRED','i');

      if _exp>0 then input @_exp p_col $40.;

      input text $200.;

      output;

    end;

run;

[Ksharp]

I gotta say data is very dirty .

filename x 'c:\temp\sampledata.txt';

data want;

infile x dlm=' ' expandtabs truncover;

input @; 

length a1-a16 $ 200;

retain a1-a16;

if prxmatch('/^\s+\d{4}\-\d{4}\-\d{4}\-\d{4}/',_infile_) then do;

   input (a1-a5) ($) a6 & $ (a8-a14) ($) ;

   a7=scan(a6,-1,' ');

   a6=substr(a6,1,findc(strip(a6),' ','b'));

end;

  else if prxmatch('/\d\d\/\d\d\/\d\d/',_infile_) then do;

   input (a2-a5) ($) a6 & $ (a8-a14) ($) ;

   if anyalpha(a5) then return;

   a7=scan(a6,-1,' ');

   a6=substr(a6,1,findc(strip(a6),' ','b'));

  end;

  else if  left(_infile_) eq: '*' then a16=_infile_;

  else if upcase(left(_infile_)) eq: 'PAYMENT' then do;

   if anydigit(a13)=1 then do;a14=a13;a13=' ';end;

   a15=_infile_;

   if not anyalpha(a5) then output; call missing(a16);

   end;

run;

Xia Keshan