topic Re: Extract string from Swift messages in SAS Programming

Extract string from Swift messages

angorwat — Wed, 08 Jun 2016 06:49:37 GMT

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.

Re: Extract string from Swift messages

Kurt_Bremser — Wed, 08 Jun 2016 05:46:47 GMT

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:/ ?

Re: Extract string from Swift messages

LinusH — Wed, 08 Jun 2016 06:47:04 GMT

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.

Re: Extract string from Swift messages

angorwat — Wed, 08 Jun 2016 06:57:58 GMT

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.

Re: Extract string from Swift messages

Ksharp — Wed, 08 Jun 2016 07:05:25 GMT

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;

Re: Extract string from Swift messages

FreelanceReinh — Wed, 08 Jun 2016 08:58:06 GMT

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.

Re: Extract string from Swift messages

LinusH — Wed, 08 Jun 2016 13:57:58 GMT

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.