topic Re: Equivalent SAS Functions Needed to Replace Teradata Functions in SAS Programming

Equivalent SAS Functions Needed to Replace Teradata Functions

ijm_wf — Wed, 07 Aug 2019 19:55:10 GMT

Can someone please suggest SAS functions that are equivalent to the following Teradata functions?

EDITDISTANCE

REGEXP_REPLACE

POSITION

REGEXP_INSTR

OREPLACE

Re: Equivalent SAS Functions Needed to Replace Teradata Functions

Tom — Wed, 07 Aug 2019 20:43:07 GMT

@ijm_wf wrote:

Can someone please suggest SAS functions that are equivalent to the following Teradata functions?

EDITDISTANCE

REGEXP_REPLACE

POSITION

REGEXP_INSTR

OREPLACE

Please provide explanation of what those functions do.

From the names I would guess that POSITION is probably INDEX or FIND (or INDEXW or FINDW).

No idea what the O in OREPLACE means but perhaps that is like the TRANWRD function?

There is a whole series perl regular expression functions in SAS.

Re: Equivalent SAS Functions Needed to Replace Teradata Functions

Reeza — Wed, 07 Aug 2019 21:11:58 GMT

You can use TERADATA functions if you use Passthrough. Not sure about EDITDISTANCE but the rest look like regex functions. I highly recommend bookmarking this page:

https://documentation.sas.com/?docsetId=lefunctionsref&docsetTarget=n01f5qrjoh9h4hn1olbdpb5pr2td.htm&docsetVersion=9.4&locale=en

You can find the functions you need under the character matching category.

Re: Equivalent SAS Functions Needed to Replace Teradata Functions

PGStats — Thu, 08 Aug 2019 03:12:51 GMT

SAS provides functions COMPGED, COMPLEV, and STEPIS for computing edit distances.

SAS main REGEX functions are called PRXPARSE, PRXMATCH, and PRXCHANGE.

Re: Equivalent SAS Functions Needed to Replace Teradata Functions

hashman — Thu, 08 Aug 2019 03:16:47 GMT

@ijm_wf:

@Tom and @Reeza have already responded with the correct suggestions wrt POSITION, OREPLACE, and REGEX*.

In Teradata, EDITDISTANCE computes the Damerau–Levenshtein distance between two strings, which is the minimal number of certain operations required to change one string into the other. SAS has 3 functions that do this type of job: SPEDIS, COMPLEV, and COMPGED. COMPLEV looks the closest since it computes the Levenshtein edit distance - but not quite, since the Damerau–Levenshtein algorithm adds the transposition operation to the classic Levenshtein.

The SAS documentation doesn't say whether COMPLEV computes the classic Levenshtein or Damerau–Levenshtein. If it's the latter, then EDITDISTANCE in Teradata is equivalent to COMPLEV in SAS. If it's the former, then COMPLEV is not it. However, I strongly suspect that the COMPGED function (GED=Generalized Edit Distance) has the arguments and flexibility to cover the Damerau–Levenshtein algorithm, and then some.

Kind regards

Paul D.