Using Proc FCMP to Create User Defined Functions for Use in SAS® Visual Analytics
Proc FCMP has been available to DATA step programmers since SAS version 9.2 and is recognised as a very valuable procedure for generating user defined functions. Out of the box, SAS has over 400 functions available. Proc FCMP allows you to extend these and write your own functions. These can be used to solve complex coding problems and be shared around the organisation for common use.
This tip explains how a user defined function, generated with Proc FCMP, can be used within SAS® Visual Analytics. An example is demonstrated, using the user defined function to generate a data field within a data query, via the Visual Data Builder.
Note, before attempting this, check that Proc FCMP is licensed at your site. For installations with only SAS Visual Analytics installed, this may not be the case.
This example was generated using SAS Visual Analytics version 7.3.
Create the Function
Create the function on your server using SAS Display Manager, SAS Enterprise Guide® software or SAS Studio. Initially set a location on the server to store the compiled function, and apply a library reference to this:
Then use Proc FCMP to create your function in this location. The example used here creates a function to calculate the number of working days between two dates. It is equivalent to the Excel function ‘networkdays’. Further details for this function can be found here.
Submitting this code creates a dataset fcmp.myfuncs which defines the function. The function is called ‘networkdays’. This particular function also requires a lookup dataset, to define non-working days. Function arguments are:
- Start Date
- End Date
- Non-working days dataset
- Date field within the non-working days dataset.
Example code to define the non-working days is given below:
To test the function within your environment, first of all set a CMPLIB options statement to point to the compiled function and then run some test code using the networkdays function:
Using the Function within a SAS Visual Analytics Query
Within SAS Visual Analytics, generate a query or modify an existing query, using the Visual Data Builder, via the ‘Prepare Data’ drop down option from the Home page.
First of all ensure that SAS can see the defined function, by setting the following libref and options statements within the Preprocess code:
The function can then be used, just like a pre-defined function. For example to set a variable ‘Working_Days’, by using the networkdays function in the Expression column.
The code generated by the Visual Data Builder, now includes a Proc SQL step that uses the networkdays function:
Saving and running the query produces the following example output table:
The field is now available for use within the Report Builder: