It is really not easy. There are too many scenarios you need to consider about :
filename xx '/folders/myfolders/incoming test file.txt';
data temp;
infile xx lrecl=2000 truncover length=len;
input x $varying2000. len;
if find(x,'INCOMING INTERCHANGE') or
find(x,'SYSTEM DATE') or
find(x,'RUN NO') or
missing(x) then delete;
length tname $ 100;
retain tname;
if scan(x,-2)='Table' then do;tname=strip(scan(x,-1,'-'));delete;end;
run;
data temp1;
set temp;
temp1=strip(tranwrd(substr(x,1,55),'09'x,' '));
p1=find(temp1,' ');
name1=substr(temp1,1,p1);
value1=strip(substr(temp1,p1));
temp2=strip(tranwrd(substr(x,56),'09'x,' '));
p2=find(temp2,' ');
name2=substr(temp2,1,p2);
value2=strip(substr(temp2,p2));
keep name1 value1 name2 value2 tname temp1 temp2;
run;
data want;
set temp1;
name=name1;value=value1;output;
name=name2;value=value2;output;
keep name value tname;
run;
proc print noobs label;run;
OUTPUT:
tname name value
Table 1 Acct Number xxxxxxxxxxxxxxxxxxx
Table 1 ZIP Code xxxxx
Table 1 Floor Limit Indicator x
Table 1 State/Prov. Code AB
Table 1 CRB/Exception File Ind
Table 1 Requested Payment Service
Table 1 PCAS Indicator x
Table 1 Number of Payment Forms
Table 1 Reference Nbr xxxxxxxxxxxxxxxxxxxxxxx
Table 1 Usage Code 1
Table 1 Business ID xxx
Table 1 Reason Code 00
Table 1 Purchase Date 20160119
Table 1 Flag 0
Table 1 Destination Amount xxxxxxxxxxxx
Table 1 Characteristics Ind N
Table 1 Currency Code 840
Table 1 Code 123456
Table 1 Source Amount 000000000100
Table 1 Capability 2
Table 1 Source Currency Code 840
Table 1 ID Method 1
Table 1 Name ABCDEFGHIJKLMNOPQRSTUV
Table 1 Flag
Table 1 City xxx-xxx-xxxx
Table 1 Entry Mode 01
Table 1 Country Code US
Table 1 Processing Date 20160121
Table 1 Category Code 1234
Table 1 Attribute 0
Table 2 Format Code
Table 2 Trace Number
Table 2 Assurance Level
Table 2 Terminal Ind
Table 2 Reference Nbr 000000
Table 2 Indicator
Table 2 Indicator
Table 2 Development Field 0
Table 2 Message Text
Table 2 Response Code
Table 2 Condition Ind
Table 2 Source Code 5
Table 2 Program Indicator
Table 2 Id Format
Table 2 Charge x
Table 2 Selection 0
Table 2 ID XXXXXXXXXXXXXXX
Table 2 Payment Count
Table 2 Terminal ID
Table 2 Identifier 123456789
Table 2 Fee 000000000000 Ca
Table 2 shback 000000000
Table 2 Pymt Ind 7
Table 2 Condition Code
Table 2 Chargeback Ind
Table 2 Environment
Table 3 Identifier 123456789 Volume Indicator
Table 3
Table 3 Amount 000000000000
Table 3 Goods Indicator
Table 3 Currency Cd 123
Table 3 Verification Val
Table 3 Response Cd
Table 3 Fee Amount 000000000000000
Table 3 Code
Table 3 Fee Sign
Table 3 ID Rsn
Table 3 Curr Ex Rate 00000000
Table 3 Processing Code
Table 3 Curr Ex Rate 00000000
Table 3 Rights Ind
Table 3 ISA Amt 000000000000
Table 3 Clearing Seq Nbr 00
Table 3 Product Id F
Table 3 Clearing Seq Cnt 00
Table 3 Program ID
Table 3 Auth Data
Table 3 Conv Ind
Table 3 Total Authorized Amount 000000000000
Table 3 Type Identification
Table 3 Information Ind
Table 3 Qualified Indicator
Table 3 Telephone Number
Table 3 Token 0000000000000000
Table 3 Data Indicator
Table 3 Result Code
Table 1 Acct Number xxxxxxxxxxxxxxxxxxx
Table 1 ZIP Code xxxxx
Table 1 Floor Limit Indicator x
Table 1 State/Prov. Code AB
Table 1 CRB/Exception File Ind
Table 1 Requested Payment Service
Table 1 PCAS Indicator x
Table 1 Number of Payment Forms
Table 1 Reference Nbr xxxxxxxxxxxxxxxxxxxxxxx
Table 1 Usage Code 1
Table 1 Business ID xxx
Table 1 Reason Code 00
Table 1 Purchase Date 20160119
Table 1 Flag 0
Table 1 Destination Amount xxxxxxxxxxxx
Table 1 Characteristics Ind N
Table 1 Currency Code 840
Table 1 Code 123456
Table 1 Source Amount 000000000100
Table 1 Capability 2
Table 1 Source Currency Code 840
Table 1 ID Method 1
Table 1 Name ABCDEFGHIJKLMNOPQRSTUV
Table 1 Flag
Table 1 City xxx-xxx-xxxx
Table 1 Entry Mode 01
Table 1 Country Code US Processing Dat
Table 1 e 20160121
Table 1 Category Code 1234
Table 1 Attribute 0
Table 2 Format Code
Table 2 Trace Number
Table 2 Assurance Level
Table 2 Terminal Ind
Table 2 Reference Nbr 000000
Table 2 Indicator
Table 2 Indicator
Table 2 Development Field 0
Table 2 Message Text
Table 2 Response Code
Table 2 Condition Ind
Table 2 Source Code 5
Table 2 Program Indicator
Table 2 Id Format
Table 2 Charge x
Table 2 Selection 0
Table 2 ID XXXXXXXXXXXXXXX
Table 2 Payment Count
Table 2 Terminal ID
Table 2 Identifier 123456789
Table 2 Fee 000000000000 Ca
Table 2 shback 000000000
Table 2 Pymt Ind 7
Table 2 Condition Code
Table 2 Chargeback Ind
Table 2 Environment
Table 3 Identifier 123456789 Volume Indicator
Table 3
Table 3 Amount 000000000000
Table 3 Goods Indicator
Table 3 Currency Cd 123
Table 3 Verification Val
Table 3 Response Cd
Table 3 Fee Amount 000000000000000
Table 3 Code
Table 3 Fee Sign
Table 3 ID Rsn
Table 3 Curr Ex Rate 00000000
Table 3 Processing Code
Table 3 Curr Ex Rate 00000000
Table 3 Rights Ind
Table 3 ISA Amt 000000000000
Table 3 Clearing Seq Nbr 00
Table 3 Product Id F
Table 3 Clearing Seq Cnt 00
Table 3 Program ID
Table 3 Auth Data
Table 3 Conv Ind
Table 3 Total Authorized Amount 000000000000
Table 3 Type Identification
Table 3 Information Ind
Table 3 Qualified Indicator
Table 3 Telephone Number
Table 3 Token
Table 3 Additional Data Indicator
Table 3 CVV2 Result Code
Table 3 Chargeback Ind
Table 3 Environment
... View more