DI Studio : Function not found : Expression editor

[JereNeal]

I am using the SAS DI expression editor (v4.901 on Windows 7) to build an expression using a function listed in the drop-down menu.

It inserts the code into the expression window with placeholders for the parameters (so it's not a function name I made up arbitrarily).

When I try to run the code, or validate the expression, I get the following error:

   Table Loader

   Line 9: ERROR: Function ISNULL could not be located.

isnull(PRC_ABBR, INPUT(PRC_ID,BEST25.))

I also get the same if I use the IF(...) function.

I've searched for similar issues in the Community blog and the ones I found don't seem to apply to this particular situation.

Here is a screenshot of the functions dropdown...

 

Here is an excerpt from the log of the 'Validate Expression' function:

102       
103        data null;
104           set work.W577RVN;
105           where isnull(PRC_ABBR, INPUT(PRC_ID,BEST25.));
ERROR: Function/subroutine ISNULL not found
106        run cancel;

-----------

In case it is significant, the expression I'm trying to enter is in the mapping portion of a table-loader transformation.

The only guess I have is that perhaps the library that contains those functions needs to be linked-to, or referenced in some way???

Any help would be appreciated.  Thanks in advance.

Jere

[LinusH · Accepted solution]

As the help text states, this is a SQL Server function.

So you can use it only if you trigger explicit SQL Pass through code to SQL Server.

Not sure what your first argument mean (PRC_ABBR).

In SAS you can use IS NULL or IS NOT NULL syntax, perhaps you can use that for your purpose?

Don't know about the IF function, might be the same.

But in SAS you'll have the IFN() and IFC() function, which probably serves you.

[JereNeal]

Linus,

Thank you for your solution.  I don't know why I didn't see the IFC and IFN, but using IFC in combination with IS NULL did the trick.

This is my first venture into the community and I'm impressed.  I hope to be able to provide answers and give back after not too long.

Anyway, Kudos!