Using FedSQL Functions

Restrictions on Function Arguments

If the value of an argument is invalid, FedSQL sets the result to a null or missing value. Here are some common restrictions on function arguments:

Some functions require that their arguments be restricted within a certain range. For example, the argument of the LOG function must be greater than 0.
Most functions do not permit nulls or missing values as arguments. Exceptions include some of the descriptive statistics functions and the IFNULL function.
In general, the allowed range of the arguments is platform-dependent, such as with the EXP function.

Using DS2 Packages in Expressions

You can invoke a DS2 package method as a function in a FedSQL SELECT statement. The syntax for the expression is shown here.

[catalog.] [schema.] package.method (argument-1 [,… argument-n])

The method parameters can be a DOUBLE, DATE, TIME, TIMESTAMP, an integer of any size, or a string.

The following restrictions apply when you use DS2 packages in expressions in FedSQL:

The method must return a value.
You must supply a package name.
The types and ordering of the arguments in the FedSQL SELECT statement must match the types and ordering of the parameters in the DS2 package method.
Package methods run from PROC FEDSQL can have only input arguments in the method.

In the following example, the method BAR is created in DS2 and then used in a FedSQL SELECT statement call as a function.

/* create table */
data dataset;
   x=1; y=1; z=1; output;
   x=1; y=1; z=2; output;
   x=2; y=2; z=2; output;
   x=2; y=2; z=8; output;
   x=2; y=3; z=13; output;
run;

/*  DS2 code */
proc ds2;
   package pkga;
      method bar(double x, double y) returns double;
         return x*x + y*y;
      end;
   endpackage;
 run;
quit;

/* fedsql code */
proc fedsql;
   select pkga.bar(1,2) as five,
      cot(radians(45)) as one,
      degrees(pi()) as one_eighty,
      power(3,4) as eighty_one,
      sign(-42) as minus1,
      sign(0) as zero,
      sign(42) as plus1;
      select * from dataset where pkga.bar(x,y) = z;
quit;

The output from the FedSQL SELECT statement would be as follows.

For more information, see Package Method Expression in SAS DS2 Language Reference.

Last updated: February 23, 2017