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
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;
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.