DATA Step, Macro, Functions and more

Extract string from Swift messages

Reply
Occasional Contributor
Posts: 18

Extract string from Swift messages

[ Edited ]

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.

Super User
Posts: 7,821

Re: Extract string from Swift messages

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,431

Re: Extract string from Swift messages

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.

Data never sleeps
Occasional Contributor
Posts: 18

Re: Extract string from Swift messages

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.

Super User
Posts: 5,431

Re: Extract string from Swift messages

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.

Data never sleeps
Super User
Posts: 10,035

Re: Extract string from Swift messages

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;
Trusted Advisor
Posts: 1,117

Re: Extract string from Swift messages

[ Edited ]

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.

Ask a Question
Discussion stats
  • 6 replies
  • 427 views
  • 0 likes
  • 5 in conversation