topic Re: Data creation from raw text in SAS Programming

Data creation from raw text

enigma84 — Sun, 09 Nov 2014 17:27:28 GMT

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

Re: Data creation from raw text

Patrick — Sun, 09 Nov 2014 20:43:37 GMT

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;

Re: Data creation from raw text

Ksharp — Mon, 10 Nov 2014 12:54:06 GMT

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