SAS - RTDM (CI STUDIO): How does it work LIKE FUNCTION?

[psmerill]

Hello everybody,

we are trying to use LIKE function in SAS RTDM (CI STUDIO) and we need help about the its syntax.

We are using this function for looking for a substring in a string and we notice that in some cases it works and in other cases not and we can't understand the reason why.

For example:

we have the following string in input:

DS ULT TREATMENT SDP = "Commerciale Conto Corrente Prova Lunghezza Max Sessanta Lung|Commerciale PostePay Evolution Prova Lunghezza Maxm Sessanta|Commerciale Finanziamento Prova Lunghezza Max Sessanta Lungh|Operativa Aggiornamento cellul Prova Lunghezza Maxm Sessanta|MustHave Consenso Privacy Prova Lunghezza Max Sessanta Lungh"

First of all, we tried with the following LIKE operator in a Filter Node: LIKE: %Conto%

than we tried with the following LIKE function: LIKE(<<DS ULT TREATMENT SDP>>,"%Conto%").

In both cases we didn't receive the expected result since that the Filter Node doesn't allow us to move to the next node even though, as you can see, the searched substring "Conto" exists in the input string (in our case "Commerciale Conto Corrente Prova Lunghezza Max Sessanta Lung|Commerciale PostePay Evolution Prova Lunghezza Maxm Sessanta|Commerciale Finanziamento Prova Lunghezza Max Sessanta Lungh|Operativa Aggiornamento cellul Prova Lunghezza Maxm Sessanta|MustHave Consenso Privacy Prova Lunghezza Max Sessanta Lungh"). 

Could you help us to find a solution? Is the function's syntax right?

Thank you in advance for your help,

Paolo

[RajaMarla · Accepted solution]

Re: Filter node.  Please review http://support.sas.com/kb/55174  and check whether you have the latest hotfixes.

You can certainly create a calculated variable - for example, use the FIND function  find(<<variable>>, 'Contol', 1, 'i') 

Check the documentation for FIND function.   If you need a more complex regex matching there are functions like PRXMATCH to help you with those.  

I do not see support for LIKE function for the calculated variable - at least not in the version I am using.

It is always helpful to know the version of the product you are working with and your database vendor/version.

[psmerill]

Hi,

We created a calculated variable with the FIND function and we received the expected result.



Thank you for your suggestion,
Paolo

[JamesAnderson]

Hi Paulo,

If you use asterix (*) with the LIKE operator in the Filter node, you should get the outcome you are looking for.

LIKE: *Conto*

Cheers

James

[psmerill]

Hi James,

thanks for your answer.

We tried to use asterix (*) with the LIKE operator in the Filter node, but we didn't receive the expected result.

Thanks,

Paolo

[ravindrrapaal]

I hope this is already answered but just making comment rather than using % you can use * for string match with Like function.
e.g Like: *Search1*