You can use a Perl regular expression to locate the letter-digits or digits-letter token and replace it to with the equivalent normalized 'letter_as_A_based_index dash digits' token.
Example:
data have;
infile datalines dlm=',' dsd truncover;
input ProductID $ ProductName :$20. ;
datalines;
2B 7890, TOYS
2-2 7890, TOYS
10D XUM, FABRICS
4-10 XUM, FABRICS
A570 PP, SUGAR
1-570 PP, SUGAR
;
data want;
set have;
rxid = prxparse ('/\b([a-z])(\d+)\b|\b(\d+)([a-z])\b/i'); * a regex pattern with | alternation;
if prxmatch(rxid, productid) then do;
cbnum = prxparen(rxid); * determine which alternation;
letter = prxposn(rxid,ifn(cbnum=2,1,4),productid); * extract parts;
digits = prxposn(rxid,ifn(cbnum=2,2,3),productid);
letterdig = rank(letter) - rank('A') + 1; * convert letter part;
if cbnum = 2 then
productid = tranwrd(productid,cats(letter,digits),cats(letterdig,'-',digits)); * replace original token with normalized token;
else
productid = tranwrd(productid,cats(digits,letter),cats(letterdig,'-',digits));
end;
drop rxid cbnum letter digits letterdig;
run;
... View more
