- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
I need to extract account number and branch number from the below swift messages. Account number present after :59: - in first input its 12345678 (until space) and branch number after ‘BSB’ is 7654.
In few records the branch number is prefixed in the account number after :59: as shown in input record 2. (acct # 21345678 BSB# 8765)
Logic rule
1) BSB should be in the :59: message field but not all records have the BSB, few records have just account number followed by account holder name(record 3)
2) 4 or 6 (with country code) digit numbers followed the text 'BSB' in the :59: field (record 1)
3) If the account number is greater than 8-digit then first 4 or 6 digit is BSB and the remaining 8 digit is account number (record 2)
4) acct # always 8-digit and BSB # always 4 or 6 digit
Record1 :SND:ABCDSASIAXXX :RCV:MNOPIN2SAXXX :20:XY1543514245054 :23B:CRED :32A:12345AUD1073,23 :50K:/1234568 XYZ P.O.BOX 316 ABCD-31952 :52A:DEFGGHRI :53B:/D/1234567800570 :57A:REWSSY2SXXX :59:/12345678 XYSDEF BSB 7654 :70:NOTES TO BANK :71A:ABC Record2 :SND:ABCDSASIAXXX :RCV:MNOPIN2SAXXX :20:XY1543514245054 :23B:CRED :32A:12345AUD1073,23 :50K:/1234568 XYZ P.O.BOX 316 ABCD-31952 :52A:DEFGGHRI :53B:/D/1234567800570 :57A:REWSSY2SXXX :59:/876521345678 XYSDEF :70:NOTES TO BANK :71A:ABC Record3 :SND:ABCDSASIAXXX :RCV:MNOPIN2SAXXX :20:XY1543514245054 :23B:CRED :32A:12345AUD1073,23 :50K:/1234568 XYZ P.O.BOX 316 ABCD-31952 :52A:DEFGGHRI :53B:/D/1234567800570 :57A:REWSSY2SXXX :59:/21345678 XYSDEF :70:NOTES TO BANK :71A:ABC
I need output as below
Acct_no BSB_no
12345678 7654
21345678 8765
Can you please advise how to achieve the desired results.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
a) could you please supply the test data in a code block, so that no smileys are created from the data?
b) what is the logical rule for the BSB to be present directly after the 59:/ ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Even though tag 59 is mandatory, and account no must be attached if known - there is to my knowledge no enforce account no format, like IBAN or BBAN.
But do you really need to parse the Swift message yourself? If you work with a financial institution they have parsers that interfaces with their payment systems.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi LinusH,
I am not sure about other available system to parse these msg in my institution. my access level is restricted to this level of data.
I need to do an analysis based on the account numbers linked to other source systems.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I think your task is if not impossible, but at least very inefficient way of solving this.
Your institution must in some place parse the Swift messages to be able to act on them.
Besides the formal structure and rules of the Swift message itself, your institution surely have some kind of algorithm to match account numbers.
Account number can be erroneous, and probably altered manually by service staff. And I guess that you want to act on the valid account no that is actually used for the transaction.
So, my advice is work out some kind of access to/extract from the database that stores the pared messages.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Assume the length of Acct_no was always 8. the length of BSB_no was always 4.
data have;
infile cards truncover;
input x $400.;
temp=substr(x,find(x,'59:/')+4);
a=scan(temp,1,' ');
if length(a)=8 then do;
Acct_no=a;
BSB_no=substr(temp,find(temp,'BSB')+4,4);
end;
else do;
Acct_no=substr(a,5);
BSB_no=substr(a,1,4);
end;
drop temp a;
cards;
ND:ABCDSASIAXXX :RCV:MNOPIN2SAXXX :20:XY1543514245054 :23B:CRED :32A:12345AUD1113 :50K:/1234568 XYZ P.O.BOX 316 ABCD-31952 :52ASmiley Very HappyEFGGHRI :53B:/D/1234567800570 :57A:RYWASY2SXXX :59:/12345678 XYSDEF BSB 7654 :70:NOTES TO BANK :71A:ABC
ND:ABCDSASIAXXX :RCV:MNOPIN2SAXXX :20:XY1543514245054 :23B:CRED :32A:12345AUD1073 :50K:/1234568 XYZ P.O.BOX 316 ABCD-31952 :52ASmiley Very HappyEFGGHRI :53B:/D/1234567800570 :57A:RYWSSY2SXXX :59:/876521345678 XYSDEF :70:NOTES TO BANK :71A:ABC
;
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi @angorwat,
Try this:
data want;
length Acct_no $8
BSB_no $6;
input @':59:/' _nbr :$20. _txt :$10. _txt @;
_l=length(_nbr);
if _l>8 then do;
Acct_no=substr(_nbr, _l-7);
BSB_no=substr(_nbr, 1, _l-8);
end;
else do;
Acct_no=_nbr;
if _txt='BSB' then input BSB_no;
end;
drop _:;
cards;
:SND:ABCDSASIAXXX :RCV:MNOPIN2SAXXX :20:XY1543514245054 :23B:CRED :32A:12345AUD1073,23 :50K:/1234568 XYZ P.O.BOX 316 ABCD-31952 :52A:DEFGGHRI :53B:/D/1234567800570 :57A:REWSSY2SXXX :59:/12345678 XYSDEF BSB 7654 :70:NOTES TO BANK :71A:ABC
:SND:ABCDSASIAXXX :RCV:MNOPIN2SAXXX :20:XY1543514245054 :23B:CRED :32A:12345AUD1073,23 :50K:/1234568 XYZ P.O.BOX 316 ABCD-31952 :52A:DEFGGHRI :53B:/D/1234567800570 :57A:REWSSY2SXXX :59:/876521345678 XYSDEF :70:NOTES TO BANK :71A:ABC
:SND:ABCDSASIAXXX :RCV:MNOPIN2SAXXX :20:XY1543514245054 :23B:CRED :32A:12345AUD1073,23 :50K:/1234568 XYZ P.O.BOX 316 ABCD-31952 :52A:DEFGGHRI :53B:/D/1234567800570 :57A:REWSSY2SXXX :59:/21345678 XYSDEF :70:NOTES TO BANK :71A:ABC
Edit: You may want to include additional checks and create log messages if unexpected values are encountered, e.g. _l>14 or _l<8.