Your raw data would work better if it was delimited (like comma, tab, pipe '|', etc). Otherwise it will be hard to find the end of the address. Let's first look at a solution that assumes it is easy to read the values. For example if the values where tab delimited.
data want ;
infile 'myfile.txt' dsd dlm='09'x truncover ;
length name $20 address $100 transaction $10 ;
input name address transaction @ ;
do col=1 by 1 until(transaction=' ');
if col=1 or transaction ne ' ' then output;
input transaction @;
end;
run;
Another possibility is that the values are space delimited, but there are always two spaces after the address and the addresses do not contain embedded double spaces. Then you can use the & modifier.
infile 'myfile.txt' truncover ;
length name $20 address $100 transaction $10 ;
input name address & transaction @ ;
If you don't have either of these then perhaps you can take advantage of the fact that your transactions always start with specific characters. Probably better to use regular expression to search for the start of the transactions, but perhaps this would also work?
data want ;
infile cards truncover ;
length name $20 address $100 transaction $10 ;
input @ ;
name = scan(_infile_,1);
loc1 = index(_infile_,'TRAN');
loc2 = index(_infile_,'SMET');
if loc1 and loc2 then loc=min(loc1,loc2);
else loc=max(loc1,loc2);
drop loc1 loc2 loc ;
address = substr(substrn(_infile_,1,loc-1),length(name)+2);
input @loc transaction @;
do col=1 by 1 until(transaction=' ');
if col=1 or transaction ne ' ' then output;
input transaction @;
end;
cards;
AMY 205ABB street NY TRAN90890 TRAN89789 SMET7485 SMET7485
TED 205BBC street CA SMET7885 SMET9885 TRAN23890 TRAN89129 SMET7655
;
... View more
