BookmarkSubscribeRSS Feed
angorwat
Calcite | Level 5

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.

6 REPLIES 6
Kurt_Bremser
Super User

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

LinusH
Tourmaline | Level 20

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
angorwat
Calcite | Level 5

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.

LinusH
Tourmaline | Level 20

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
Ksharp
Super User

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;
FreelanceReinh
Jade | Level 19

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 2454 views
  • 0 likes
  • 5 in conversation